If you simply want to reduce the number of features to load in each iteration, why do you need to use a spatial predicate, you can determine the count of records (~130,000) then use: select ... limit .... offset ....;
Iterate through until done. Your features will not be spatially grouped, but will be in manageable chunks. If some level of spatial grouping is required, you could just add in "order by ST_XMin(the_geom)" to get output grouped by X axis (longitude) That aside, I think your ST_contains() has the arguments in reverse, so you are asking for all features that contain your polygon, not the other way around, and zero sounds like the correct answer. http://postgis.refractions.net/documentation/manual-svn/ST_Contains.html Also, note that any of your features that cross the boundary of a box will never be returned, as they are not contained by any single box. HTH, Brent Wood --- On Tue, 12/7/10, Eric Ladner <eric.lad...@gmail.com> wrote: From: Eric Ladner <eric.lad...@gmail.com> Subject: [postgis-users] New to postgis... To: postgis-users@postgis.refractions.net Date: Tuesday, December 7, 2010, 4:41 PM I just started using postgis in conjunction with OpenStreetMap and I've been having a heck of a time gettnig a query to work. I'm fairly well versed in SQL in general (Oracle DBA for 10+ years) but I'm new to the spatial stuff. I've got a bunch of plot line data downloaded from the state's GIS system loaded into a postgis database and I was trying to slice it up into manageable chunks to upload to OSM. In the table, I've got almost 130,000 distinct objects and I can view them with queries (like below... I shortened up the MULTIPOLYGON list for brevity..) select gid, ST_AsText(the_geom) from gis_schema.jksn_cnty_buidings where gid = 99; gid | st_astext -----+----------------- 99 | MULTIPOLYGON(((-88.7426828988773 30.40751849384,-88.7427319811994 30.4075187592759,....-88.7426828988773 30.40751849384))) The theory goes like this: Find the extents of the data (xmax, ymax, xmin, ymin - this part works great), select min(ST_XMin(the_geom)) as "min_x", max(ST_XMax(the_geom)) as "max_x" from gis_schema.jksn_cnty_buidings ; select min(ST_YMin(the_geom)) as "min_y", max(ST_YMax(the_geom)) as "max_y" from gis_schema.jksn_cnty_buidings ; Then partition that space into 16 or 25 sub-areas with a Perl script that generates individual queries for the sub quadrants.. I planned on moving them to additional numbered tables (to avoid dupes when objects overlap the bounding polygon) then to handle each table as a separate upload. The examples below are just selects, though, all of which return zero rows. select * from gis_schema.jksn_cnty_buidings where _ST_Contains(the_geom,'POLYGON ((-88.981 32.0393, -88.8616 32.0393, -88.8616 32.4663, -88.981 32.4663, -88.981 32.0393))'); select * from gis_schema.jksn_cnty_buidings where _ST_Contains(the_geom,'POLYGON ((-88.8616 32.0393, -88.7422 32.0393, -88.7422 32.4663, -88.8616 32.4663, -88.8616 32.0393))'); There are thousands of polygons all across the area, and I've hand verified a couple. The above two queries should return about 2300 records each, but every time I run it, it returns zero. I've tried several variants like (including _ST and regular ST functions) select * from gis_schema.jksn_cnty_buidings where the_geom && 'POLYGON ((-88.5034 32.0393, -88.384 32.0393, -88.384 32.4663, -88.5034 32.4663, -88.5034 32.0393))' and _ST_Contains(the_geom,'POLYGON ((-88.5034 32.0393, -88.384 32.0393, -88.384 32.4663, -88.5034 32.4663, -88.5034 32.0393))'); And even select gid, objectid from gis_schema.jksn_cnty_buidings where ST_Within(the_geom,ST_AsText(ST_MakeEnvelope(-90, 30, -86, 30, 4269))); An ideas where I'm messing up here? -- Eric Ladner _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users