As a follow-up to my own email below, I ran the following query

SELECT COUNT(py.poly_id)
FROM polys py JOIN points pt ON
 (py.xmin < pt.x AND py.ymin < pt.y AND py.xmax > pt.x AND py.ymax > pt.y);

to determine how many exact points-in-poly matches I could find. The
query has now been running for the past couple of hours, no end in
sight. So, I am not even sure if UPDATEing the points.name column
based on this logic would be helpful.

In any case, suggestions welcome.

On 8/26/06, P Kishor <[EMAIL PROTECTED]> wrote:
Greets,

Using SQLite for Windows 3.3.7. I have the following two tables
-- bounding box of each poly
CREATE TABLE polys (
  poly_id INTEGER PRIMARY KEY,
  xmin    REAL,
  ymin    REAL,
  xmax    REAL,
  ymax    REAL,
  name    TEXT
)

data look like so

1|1723885.18957644|282631.95646140|1727224.46537863|287816.54753434|100030117004
2|1716073.04710809|281166.39606662|1725746.97483727|287098.00276086|100030118009

-- coord pair of each point
CREATE TABLE points (
  point_id  INTEGER PRIMARY KEY,
  x         REAL,
  y         REAL,
  name      TEXT
)

data look like so

1|-2268900.28781180|191367.60670709|
2|-2269660.73941476|193426.66511514|

I have built the following indexes

CREATE INDEX ix_polys_xmin ON polys (xmin)
CREATE INDEX ix_polys_ymin ON polys (ymin)
CREATE INDEX ix_polys_xmax ON polys (xmax)
CREATE INDEX ix_polys_ymax ON polys (ymax)
CREATE INDEX ix_points_x ON points (x)
CREATE INDEX ix_points_y ON points (y)

Here is what I want to do: I want to SET points.name = polys.name WHERE

  polys.xmin < (SELECT points.x FROM points WHERE point_id = ?) AND
  polys.ymin < (SELECT points.y FROM points WHERE point_id = ?) AND
  polys.xmax > (SELECT points.x FROM points WHERE point_id = ?) AND
  polys.ymax > (SELECT points.y FROM points WHERE point_id = ?)

yields one and only one result. For all other records, I want to run a
further point-in-poly function which enables me to find an exact
match. In other words, I minimize the number of times I have to run my
point-in-poly function by getting SQLite's help in eliminating the
points I am know to definitely fall inside a specific poly.

So, to test my approach, I set up a loop over the points (I am using
Perl DBI), and tried the following --

  foreach point_id

    SELECT name
    FROM polys
    WHERE
      xmin < (SELECT x FROM points WHERE point_id = ?) AND
      ymin < (SELECT y FROM points WHERE point_id = ?) AND
      xmax > (SELECT x FROM points WHERE point_id = ?) AND
      ymax > (SELECT y FROM points WHERE point_id = ?)

    If only one polys.name was found, that's it

    elsif more than one polys.name were found
      run another function to determine exact match

Well, I learned that perhaps this may not be the way to do this. You
see, I have more than 200k rows in my polys table, and more than 5
million rows in the points table. I ran the above query last night,
went to sleep, and this morning it was still churning away (or had
frozen the 'puter). I had to kill it. Looping over each point is just
way too slow.

Another approach would be to loop over each poly and narrow my set to
all the points in it. Then run my function on each point against that
poly. However, this will also likely take very long -- one, I have to
still loop over each row, and then, when I find the points, I have
check each point (as I might have overlapping polys).

Suggestions?

--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.ies.wisc.edu/
Open Source Geospatial Foundation https://edu.osgeo.org/



--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.ies.wisc.edu/
Open Source Geospatial Foundation https://edu.osgeo.org/

-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------

Reply via email to