Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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');
share|improve this question
add comment

1 Answer

up vote 8 down vote accepted

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.