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...
... I now get those weird ones:
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.
