2

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:

ALTER TABLE myTable1
ADD CONSTRAINT pk_myTable1 PRIMARY KEY CLUSTERED (FULL_ADDRESS);

ALTER TABLE myTable1 
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:

SELECT DISTINCT
      mt1.FULL_ADDRESS
    , mt1.LONGITUDE
    , mt1.LATITUDE
    , mt2.POLYGON_NAME
INTO #TEMP_TABLE
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"^
 "MSSQL:server=DestServerName;database=DestDbName;trusted_connection=yes"^
 "MSSQL:server=SourceServerName;database=SourceDbName;trusted_connection=yes"^
 -sql "SELECT [POLYGON_NAME], [geom].STAsText() geom FROM [SourceDbName].[dbo].[myTable2]"^
 -nln "myTable2"^
 -overwrite^
 -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"^
 "MSSQL:server=DestServerName;database=DestDbName;trusted_connection=yes"^
 "MSSQL:server=SourceServerName;database=SourceDbName;trusted_connection=yes"^
 -sql "SELECT [POLYGON_NAME], [geom].STAsText() geom FROM [SourceDbName].[dbo].[myTable2]"^
 -nln "myTable2"^
 -overwrite^
 -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):

ALTER TABLE myTable2
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 '18 at 9:51
  • How does it work precisely? I'm kind of a newbie, sorry :/ – Guillaume Nov 6 '18 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. – Andreas Müller Nov 6 '18 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 '18 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 '18 at 16:35

Your Answer

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

Browse other questions tagged or ask your own question.