how to create SQL View in Geoserver with command line

Question

The following is the code I use, which does not work

curl -v -u admin:geoserver -XPUT -H 'Content-type: text/xml' -d
'<featureType>
<name>h12v10_locations_20ztesting</name>
<nativeName>h12v10_locations_20ztesting</nativeName>
<srs>EPSG:4326</srs>
<metadata><entry>
<virtualTable><name>h12v10_locations_20ztesting</name>
<sql>select gid, poly_label, data_score, data_time, year, the_geom
from h12v10_locations_2002
order by gid asc</sql>
<keyColumn>feature_id</keyColumn>
<geometry><name>the_geom</name><type>POINT</type><srid>4326</srid></geometry>
</virtualTable></entry></metadata></featureType>'
http://localhost:8080/geoserver/rest/layers/cite:h12v10_locations_20ztesting.xml

I want to use sql view than creating layer is that when I update data is PostGIS, the order of data entries retrieved by client will change. For example, when I update id=1, the first entries retrieved will become id=2 from id=1

Jordain

Answer 1

I found the solution after some trial and error ^_^

curl -v -u admin:geoserver -XPOST -H 'Content-type: text/xml' -d '
<featureType>
<name>h12v10_locations_2004new</name>
<nativeName>h12v10_locations_2004new</nativeName>
<namespace>
<name>cite</name>
<atom:link xmlns:atom="http://www.w3.org/2005/Atom" rel="alternate" href="http://arizona-umh.cs.umn.edu:8080/geoserver/rest/namespaces/cite.xml" type="application/xml"/>
</namespace>
<title>h12v10_locations_2004new</title>
<keywords>
<string>features</string>
<string>h12v10_locations_2004new</string>
</keywords>
<srs>EPSG:4326</srs>
<projectionPolicy>FORCE_DECLARED</projectionPolicy>
<enabled>true</enabled>
<metadata>
<entry key="cachingEnabled">false</entry>
<entry key="JDBC_VIRTUAL_TABLE">
<virtualTable>
<name>h12v10_locations_2004new</name>
<sql>select gid, poly_label, data_score, data_time, year, the_geom from h12v10_locations_2004 order by gid asc</sql>
<escapeSql>false</escapeSql>
<keyColumn>gid</keyColumn>
<geometry>
<name>the_geom</name>
<type>Point</type>
<srid>4326</srid>
</geometry>
</virtualTable>
</entry>
</metadata>
<store class="dataStore">
<name>AlwaysShen</name>
<atom:link xmlns:atom="http://www.w3.org/2005/Atom" rel="alternate" href="http://arizona-umh.cs.umn.edu:8080/geoserver/rest/workspaces/cite/datastores/AlwaysShen.xml" type="application/xml"/>
</store>
<maxFeatures>0</maxFeatures>
<numDecimals>0</numDecimals>
</featureType>'
http://localhost:8080/geoserver/rest/workspaces/cite/datastores/AlwaysShen/featuretypes/

Hope this may help those guys who spent hours and days googling and found nothing really solve this specific problem:)

Jordain

Is there any way to guess the coordinate system in such implementation? or way to define the native SRS? I published my layer successfully but due to wrong SRID, I am getting this error, java.lang.RuntimeException: org.xml.sax.SAXParseException; Premature end of file. org.xml.sax.SAXParseException; Premature end of file. Premature end of file. – Sean