7

I'm having difficulty with a PostGIS query. I am trying to query my database (world_countries) using a bounding box drawn on a google web map. The objective of this query is to find any countries that lie within a box drawn by the user, and to return them in GeoJSON format. The query is as follows:

SELECT ST_AsGeoJSON(the_geom)
 FROM
 "world_countries"
WHERE 
((the_geom && 'POLYGON((
-1572563.9181513 7593219.6338481, 
-1709539.0728192 6507202.3361235, 
-413167.07328311 6477850.5172661, 
-271299.948805577549191.905562, 
-1572563.9181513 7593219.6338481))'));

and the error message is:

ERROR: Operation on two geometries with different SRIDs
SQL state: XX000

The SRID of this data is 900913 (srid(the_geom) = 900913). When I look at this data in ArcGIS and pan around, the values from the box above match up with ArcGIS.

Is there a reason why a polygon geometry requires a specific SRID? I assumed that my values were within the range.


A quick note about my projection. I originally had a map of countries of the world in geometric form using WGS_84. I then used ArcGIS to re-project this into 'WGS 1984 Web Mercator.prj' based on this thread.

As I am using the following version of PostGIS "POSTGIS="1.3.6" GEOS="3.1.1-CAPI-1.6.0" PROJ="Rel. 4.7.1, 23 September 2009" USE_STATS" I ran the following SQL to include the google projection info:

INSERT into spatial_ref_sys (srid, auth_name, auth_srid, srtext, proj4text) values (900913 ,'EPSG',900913,'GEOGCS["WGS 84", DATUM["World Geodetic System 
1984", SPHEROID["WGS 84", 6378137.0, 298.257223563,AUTHORITY["EPSG","7030"]], AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich", 0.0, AUTHORITY["EPSG","8901"]], NIT["degree",0.017453292519943295], AXIS["Longitude", EAST], AXIS["Latitude", NORTH],AUTHORITY["EPSG","4326"]], PROJECTION["Mercator_1SP"],PARAMETER["semi_minor", 6378137.0], 
PARAMETER["latitude_of_origin",0.0], PARAMETER["central_meridian", 0.0], PARAMETER["scale_factor",1.0], PARAMETER["false_easting", 0.0], PARAMETER["false_northing", 0.0],UNIT["m", 1.0], AXIS["x", EAST], AXIS["y", NORTH],AUTHORITY["EPSG","900913"]] |','+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m 
+nadgrids=@null +no_defs');
8

Try replacing

'POLYGON((-1572563.9181513 7593219.6338481, 

with

'SRID=900913;POLYGON((-1572563.9181513 7593219.6338481...

The first definition does not list the SRID so it's not using a SRID at all, hence the mismatch with 900913:

osm=# select srid('POLYGON((1 1,2 2,3 3,4 4,1 1))'::geometry);
 srid 
------
   -1

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.