postgis provider needs to use a spatial index

[normanb]

In PostGIS.js _buildSchemaFromFeature needs to add a geometry column, http://postgis.refractions.net/docs/AddGeometryColumn.html so that the queries are using a spatialindex. Just noting this, I am adding sqlite as a db provider and I will put the fix in both.

[chelm]

@normanb thanks! You are correct that that the spatial index needs to be created, however the koop cache uses json datatypes for each row which means we dont need to create a geom column in postGIS but rather we should create the spatial index on the geojson geom directly. I have an issue to add this. Should happen soon.

[normanb]

@chelm I can create a PR for this, I have to do it anyway for sqlite.

On Tue, May 27, 2014 at 9:41 AM, Christopher Helm notifications@github.comwrote:

@normanb https://github.com/normanb thanks! You are correct that that the spatial needs to be created, however the koop cache uses json datatypes for each row which means we dont need to create a geom column in postGIS but rather we should create the spatial index on the geojson geom directly. I have an issue to add this. Should happen soon.

— Reply to this email directly or view it on GitHubhttps://github.com/Esri/koop-server/issues/2#issuecomment-44293650 .

[normanb]

@chelm this is going to need a separate geometry column but it can be populated with st_geomfromgeojson.

[chelm]

@normanb yeah I researched it last night and I agree. Super easy fix to add.

[chelm]

@normanb I've implemented this half way. That is Koop is now creating the proper columns, creating indexes, and inserting geometries into the column for every table. However it is not yet actually taking advantage of them. Still selecting the geojson geoms. Completion coming soon.

[normanb]

@chelm, ok I will stop working on my version of this then :) Back to sqlite :)

On Thu, May 29, 2014 at 1:50 PM, Christopher Helm notifications@github.com wrote:

@normanb https://github.com/normanb I've implemented this half way. That is Koop is now creating the proper columns, creating indexes, and inserting geometries into the column for every table. However it is not yet actually taking advantage of them. Still selecting the geojson geoms. Completion coming soon.

— Reply to this email directly or view it on GitHub https://github.com/Esri/koop-server/issues/2#issuecomment-44576431.

[chelm]

Hah cool.

https://github.com/Esri/koop-server/blob/master/lib/PostGIS.js#L381

[normanb]

@chelm thanks for fixing the tests. You are using a geography type in PostGIS which uses geodetic calculations on the ellipsoid? Isn't a geometry sufficient, this would then support other upstream projections without any code change. I guess the new ticket is support projections!