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?

Improve this question
4
  • 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
    Commented Jan 13, 2018 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
    Commented Jan 14, 2018 at 11:39
  • Is the table using EPSG:4283? Remember that SQL Server doesn't support coordinate transformation
    – Peter Horsbøll Møller
    Commented Jan 18, 2018 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
    Commented Jan 18, 2018 at 19:21

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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