Create Polygon layer on GeoServer using SQL View Query

Question

I am drawing polygon on map using openlayer and storing geojson into sql server DB and From Stored geojson i want to create polygon layer on GeoServer and again using wms request to reflect polygon image tile on Map.

So draw on map and storing into db working fine.

Table column EntityJson look like

{
  "LatLong": {
        "Type": "string",
        "Value": "{ \"type\": \"Polygon\", \"coordinates\": [ [ [ -76.2890625, 58.07787626787517 ], [ -95.625, 50.28933925329178 ], [ -84.72656249999999, 41.244772343082076 ], [ -62.22656249999999, 43.32517767999296 ], [ -56.6015625, 48.45835188280866 ], [ -53.78906249999999, 56.9449741808516 ], [ -63.28125, 62.91523303947614 ], [ -76.2890625, 58.07787626787517 ] ] ] }"
    }
}

and EntityType is "Area".

But for Next flow to call WMS request on client side for that i'm trying to create polygon Layer by using sql view query on GeoServer so that dynamic take geojson from db based on where EntityType="Area".

But i didn't get how to create sql view query for polygon layer to get geojson data from SqlDB table column EntityJson.

Please can anyone tell me. How to get polygon geojson from table column and create polygon layer on GeoServer by using sqlview query.

Thanks.

Ian Turton Ian Turton · Accepted Answer · 2017-11-07T08:43:48

The only way to do this is to use string replacement to construct a WKT polygon definition from the JSON which is going to be slow and error-prone.

A better way is to not store GeoJSON in the database but instead store polygons. The easy way to do this is to use OpenLayers WFS-T support to send the features to GeoServer which will then write them correctly into the database (in a vendor-neutral way should you later change databases) and will be able to access them via the WMS interface with no extra effort.

Alternatively, if you have to use GeoJson as a transport format then you should probably look at a NoSQL database like mongoDB which is also usable as a data store in GeoServer.

Create Polygon layer on GeoServer using SQL View Query

1 Answers