Creating a SQL view

Creating a SQL view in GeoServer is not different from creating one in an RDBMS. You just have to build proper SQL code to filter and transform the feature.

It is not mandatory that source tables for your view are already published in GeoServer. You only need to have a data store properly configured to an RDBMS; you can't use a SQL view against a shapefile or other file-based stores.

As you can see in the SQL code for this recipe, you can use any combination of the standard SQL and vendor extension. GeoServer does not evaluate the code, but it demands parsing and evaluation to the RDBMS.

You can use the aggregation and transformation function as we did. You need to return at least a proper geometrical field so that GeoServer can evaluate it and use it to configure a layer.

The view created from GeoServer is not stored inside your RDBMS. This may sound odd if you're used to creating views in a database. Indeed, GeoServer views are a sort of virtual table. You can check this inside the data directory. Look for the workspace and find the featuretype definition, which is in the featuretype.xml file. You will find that your SQL query is just stored inside it:

  <metadata>
    <entry key="JDBC_VIRTUAL_TABLE">
      <virtualTable>
        <name>EuropeanCountries</name>
        <sql>SELECT 
  ADMIN,
  ST_UNION(COUNTRIES_EXP.GEOM) AS GEOM
FROM 
  (SELECT 
     ADMIN, 
     (ST_DUMP(GEOM)).geom as geom
   FROM 
     COUNTRIES 
   WHERE 
     REGION_UN = &apos;Europe&apos;) COUNTRIES_EXP 
WHERE 
  ST_Intersects(COUNTRIES_EXP.GEOM, ST_GeomFromText(&apos;POLYGON((-11 37.40, -11 73.83, 27.28 73.83, 27.28 37.40, -11 37.40))&apos;,4326)) = TRUE 
GROUP BY ADMIN</sql>
        <escapeSql>false</escapeSql>
        <geometry>
          <name>geom</name>
          <type>MultiPolygon</type>
          <srid>4326</srid>
        </geometry>
      </virtualTable>
    </entry>
  </metadata>