[postgis-users] New to postgis...

[pcreso at pcreso.com]

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.ladner at gmail.com> wrote:

From: Eric Ladner <eric.ladner at gmail.com>
Subject: [postgis-users] New to postgis...
To: postgis-users at 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 at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20101206/ef2b6670/attachment.html>