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');
