2

I am trying to run a basic point in polygon query against a table of polygons. In PostGIS this works:

SELECT *
FROM schema.table
WHERE ST_Contains(
  table.geom,
  ST_PointFromText('POINT(XXX.XXXXXXX YY.YYYYYY)',4283)
);

As I understand it, this is the equivalent in SQL Server:

SELECT *
FROM schema.table
WHERE column.STContains(
  geography::STPointFromText('POINT(XXX.XXXXXXX YY.YYYYYY)',4283)
) = 1;

Both tables are generated from exactly the same shapefile. For PostGIS I use shp2pgsql and for SQL Server I use ogr2ogr to convert the data. In SQL Server I need to run a query to MakeValid the 8 errors in the source data (confirmed in QGIS).

When I run the above query in PostGIS I am returned a single, correct polygon. When I run the other query in SQL Server I am returned a set of polygons, none of which is correct.

Can someone assist me in getting the query correct for SQL Server?

  • Can you give us an example with a reduced use case? Perhaps with just the the row PostGIS returns and a row that SQL Server returns? It hard to tell what Microsoft is doing from a pseudocode example. – Evan Carroll Jan 13 '18 at 20:06
  • Here's the export from SQL Server and the export from PostGIS. The full PostGIS query is SELECT * FROM public.electorates WHERE ST_Contains( electorates.geom, ST_PointFromText('POINT(151.0424707 -33.7395596)',4283) ); – Jeffery Candiloro Jan 14 '18 at 11:39
  • Is the table using EPSG:4283? Remember that SQL Server doesn't support coordinate transformation – Peter Horsbøll Møller Jan 18 '18 at 9:38
  • Yes, the table is using EPSG:4283. I have also used ogr2ogr to reproject to EPSG:4326 when performing the input with the same net result. – Jeffery Candiloro Jan 18 '18 at 19:21
1

Sort of figured it out. Looks like it is a problem in geography vs geometry. If I import the polygons as geometries then it works properly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.