2

I'm currently trying to import a bunch of boundaries into a legacy system with a specific SRID. its a mySQL db.

After import and running some intersect code, i receive this error:

Binary geometry function st_intersects given two geometries of different srids: 1 and 0, which should have been identical.

As it says, there is an srid mismatch of 1 vs 0. The newly imported boundaries are SRID 1, the existing boundaries from another table (cannot be changed) are SRID 0. I have established this with mysql command

select ST_SRID(boundary_polygon) from boundaries; 

My question, what EPSG value would i use with ogr2ogr's -a_srs flag. I want to force SRID=0?

Here is the current command i'm using for import:

ogr2ogr -f "MYSQL" MYSQL:"blahdb,host=localhost,user=blah,password=blah,port=3306" boundaries.tab

I'm currently running this command which forces the import to SRID=0 which seems by convention to mean No SRID.

UPDATE boundaries SET boundary_polygon = GeomFromWKB(boundary_polygon, 0);

This then allowed my st_intersects method to work fine and find intersect between two polygons.

However, I still need a way to import as SRID=0 from ogr2ogr instead of SRID=1. So that I don't need this post import SQL step

  • You can try to add -a_srs EPSG:0 into your ogr2ogr command but I fear that it fails. – user30184 Apr 12 '17 at 10:24
  • @user30184 no when i try with EPSG:0 i get error, ERROR 1: Failed to lookup UOM CODE 0 – wired00 Apr 12 '17 at 10:39
  • GDAL driver page gives only this information: SRS information is stored using the OGC Simple Features for SQL layout, with geometry_columns and spatial_ref_sys metadata tables being created in the specified database if they do not already exist. The spatial_ref_sys table is not pre-populated with SRS and EPSG values like PostGIS. If no EPSG code is found for a given table, the MAX(SRID) value will be used. I would check what spatial_ref_sys contains. Perhaps you can fiddle the MAX(SRID) into "0", – user30184 Apr 12 '17 at 11:01
  • Interesting, when i select from spatial_ref_sys it indeed only has a single record with SRID = 1. I assume if I change this value, it will default to 0 from then on? – wired00 Apr 13 '17 at 2:01
  • @user30184 yep ok, so manipulating the spatial_ref_sys allowed changing default to 0. – wired00 Apr 13 '17 at 2:06
1

The behavior with SRIDs in the GDAL MySQL driver is not very clearly explained on the driver manual page http://www.gdal.org/drv_mysql.html. A hint can be found from the "Caveats" section

SRS information is stored using the OGC Simple Features for SQL layout, with geometry_columns and spatial_ref_sys metadata tables being created in the specified database if they do not already exist. The spatial_ref_sys table is not pre-populated with SRS and EPSG values like PostGIS. If no EPSG code is found for a given table, the MAX(SRID) value will be used.

What probably happens when you save data into MySQL for the first time with an unknown SRID is that GDAL does not find the spatial_ref_sys metadata table. Because the table does not exist it is created and one row with SRID=1 is inserted. Next time the metadata table exists and value MAX(SRID)=1 will be used when new data with unknown projection are saved into new tables.

A workaround, kindly tested by user @wired00, is to make one conversion with some dummy data with GDAL so that spatial_ref_sys table appears and update the SRID from 1 to 0. Now MAX(SRID)=0 and zero will be used as SRID in following conversions.

  • Yes that's right by merely editing the single srid row in spacial_ref_sys from 1 to 0 it would auto default to that projection. – wired00 Apr 13 '17 at 19:58

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.