[Gdal-dev] -spat switch in ogr2ogr
brian at thetimoneygroup.com
Wed Oct 25 14:46:37 EDT 2006
Frank Warmerdam wrote:
> Brian Timoney wrote:
>> At the risk of disproving the adage that the only dumb question is
>> the one left unasked, I'm having some trouble applying the -spat
>> switch using ogr2ogr from the command line.
>> I have a table of US cities in PostGIS called..."cities" in lat/long
>> NAD 83 (EPSG:4269)
>> I want to pull cities from the middle of the country from a BBOX of
>> (-114,30,-100,45) and throw them into a new shapefile.
>> So I hazarded...
>> ogr2ogr mid_city.shp PG:'dbname=mydb user=me' cities -spat '-114 30
>> -100 45'
>> No dice (empty shapefile). There are 75+ features that fall inside
>> the bounding box.
>> I've also tried double quotes and no quotes around the xmin ymin xmax
>> ymax values.
> It should work with:
> ogr2ogr mid_city.shp PG:'dbname=mydb user=me' cities -spat -114 30
> -100 45
> Would it be fair for me to guess that the following works fine?
> ogr2ogr mid_city.shp PG:'dbname=mydb user=me' cities
> So if my suggested approach (no quotes on -spat argument) doesn't
> work, then
> I'm at a bit of a loss. I guess I'd have to actually test things at
> my end!
> By the way, you might want to set the CPL_DEBUG environment variable
> to ON.
> This will produce various debug output of the actual SQL being sent to
> postgis. This might give clues.
> Best regards,
OK, switching the debugging on--
$ CPL_DEBUG=ON ogr2ogr mid_city.shp PG:'dbname=mydb user=me' cities
-spat -114 30 -100 45
Gave me what ogr2ogr is passing to PostGIS. The key part is the WHERE
WHERE the_geom && GeometryFromText('BOX3D(-114.000000
I fooled around replicating the WHERE statement in PgAdmin's SQL window
and ran into the following issues--
1) The GeometryFromText function no longer honors 'BOX3D'
2) Following the suggestion above, I tried
WHERE the_geom && SetSRID('BOX3D(-114 30,-100 45)::box3d,-1)
and received an error for "Operation on two geometries with different SRIDs"
3) Since my PostGIS table has an SRID of 4269, I tried
WHERE the_geom && SetSRID('BOX3D(-114 30,-100 45)::box3d,4269)
and everything worked swimmingly....
I hope the above helps,
The Timoney Group
518 17th Street
Denver, CO 80202
Ph: (303) 929-3722
Fax: (303) 571-4304
More information about the Gdal-dev