This question is more or less a sequel to Performance of joining tables on a geometry column with SQL Server

As explained in the above link, I have a shapefile that I upload to my SQL Server DB using the Shape2Sql.exe tool. Then, I'm checking whether my sets of coordinates are located inside one of the polygons given by the shapefile.

The situation has however slightly changed because I now take into account the remarks I got, which still gives this:

CREATE TABLE dbo.myTable1 (
       FULL_ADDRESS varchar(120) NULL,
       LONGITUDE varchar(50) NULL,
       LATITUDE varchar(50) NULL

CREATE TABLE dbo.myTable2 (
       POLYGON_NAME nvarchar(255) NULL,
       geom geometry NULL

But I then do this:


ADD geom geometry;

UPDATE myTable1 
SET geom=geometry::STGeomFromWKB((geography::Point(LATITUDE, LONGITUDE, 4326)).STAsBinary(), 4326); 

CREATE SPATIAL INDEX table_gist ON myTable1(geom) 
    WITH (BOUNDING_BOX = (minLong, minLat, maxLong, maxLat)) ON [PRIMARY];
CREATE SPATIAL INDEX table_gist_2 ON myTable2(geom) 
    WITH (BOUNDING_BOX = (minLong, minLat, maxLong, maxLat)) ON [PRIMARY];

After that, I can join my tables with some pretty good performance:

    , mt1.LONGITUDE
    , mt1.LATITUDE
    , mt2.POLYGON_NAME
FROM myTable1 mt1
LEFT JOIN myTable2 mt2 
  ON mt2.geom.STIntersects(mt1.geom) = 1

With my first shapefile, it was perfectly working. Then I had to play with another file and it all of a sudden stopped being fun. I couldn't find any set of coordinates intersecting my polygons. I eventually ended up locating the problem: while my first shapefile was encoded in EPSG:4326, the second one uses ESRI:103300.

Therefore, instead of having those nice coordinates that I'm used to...

EPSG:4326 polygons

... I now get those weird ones:

ESRI:103300 polygons

It surprised me because, when using my Shape2Sql.exe tool, I always check the 'Set SRID 4326' option. I also tried the following line of code:

UPDATE myTable2 SET geom.STSrid=4326;

But it doesn't work either. That doesn't change anything to my polygons.

I'd like to find a trick so that my query can find in which polygons my points are located, like it used to do.

EDIT: As suggested in the comments, I gave a try at the ogr2ogr solution with the following command that was inspired by the Is it possible to reproject spatial data using SQL Server? topic:

C:\OSGeo4W64\bin\ogr2ogr.exe ^
 -f "MSSQLSpatial"^
 -sql "SELECT [POLYGON_NAME], [geom].STAsText() geom FROM [SourceDbName].[dbo].[myTable2]"^
 -nln "myTable2"^
 -s_srs ESRI:103300^
 -t_srs EPSG:4326

But I now have this error message:

ERROR 1: Failed to process SRS definition: ESRI:103300

EDIT2: This version however seems to work:

C:\OSGeo4W64\bin\ogr2ogr.exe ^
 -f "MSSQLSpatial"^
 -sql "SELECT [POLYGON_NAME], [geom].STAsText() geom FROM [SourceDbName].[dbo].[myTable2]"^
 -nln "myTable2"^
 -s_srs http://spatialreference.org/ref/esri/103300/^
 -t_srs http://spatialreference.org/ref/epsg/4326/

I then did this (I followed the same instructions):

ADD geom2 geometry;

UPDATE myTable2
SET geom2 = geometry::STGeomFromText(geom, 103300);

But it gives me this:

Implicit conversion from data type geometry to nvarchar is not allowed. Use the CONVERT function to run this query.

  • you need to reproject the points not simply label them as being lat/lon
    – Ian Turton
    Nov 6, 2018 at 9:51
  • How does it work precisely? I'm kind of a newbie, sorry :/
    – Guillaume
    Nov 6, 2018 at 10:03
  • You are used to geographic coordinates, but now you have planar and metric coordinates. So steps to be undertaken are to transform your coordinates into geographic coordinates and change the underlying datum. I think postgis can do this for you, but it may not know ESRI:103300. Theres a code on epsg.io/103300 to make this known to postgis. Nov 6, 2018 at 10:04
  • There is no solution for SQL Server then? I read that the sys.spatial_reference_systems table can't be changed in SQL Server. How about the reprojection suggested by @IanTurton? How can I do that?
    – Guillaume
    Nov 6, 2018 at 15:11
  • I don't use SQL Server, but maybe Shape2Sql.exe takes an option to reproject, or check out ogr2ogr which will
    – Ian Turton
    Nov 6, 2018 at 16:35


Your Answer

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

Browse other questions tagged or ask your own question.