I have the following two tables and related indexes --
CREATE TABLE IF NOT EXISTS pt (
id INTEGER PRIMARY KEY,
x REAL,
y REAL,
attr TEXT
)
CREATE INDEX ix_pt_x ON pt (x)
CREATE INDEX ix_pt_y ON pt (y)
CREATE TABLE IF NOT EXISTS py (
id INTEGER PRIMARY KEY,
xmin REAL,
ymin REAL,
xmax REAL,
ymax REAL,
attr TEXT
)
CREATE INDEX ix_py ON py (xmin, ymin, xmax, ymax)
I want to UPDATE pt SETting pt.attr = py.attr WHEREver the Count of
(pt.x BETWEEN py.xmin AND py.xmax AND pt.y BETWEEN py.ymin AND
py.ymax) is 1.
So, I have come up with the following way -- I have created a view to
do my SELECTs
CREATE VIEW v_attr AS
SELECT pt.id AS pt_id, Count(pt.id) AS pt_id_count, py.attr AS py_attr
FROM pt JOIN py ON
(
(pt.x BETWEEN py.xmin AND py.xmax) AND
(pt.y BETWEEN py.ymin AND py.ymax)
)
GROUP BY pt_id_count
HAVING pt_id_count = 1
)
Well, any SELECTs from the view v_attr take forever. Anyway to speed it up?
If all of the above works, then I can do the following UPDATE --
UPDATE pt AS a_pt
SET attr = (
SELECT py_attr
FROM v_attr
WHERE a_pt.id = v_attr.pt_id
)
And so I ask you, does this make sense? Is my JOIN okay? One local wag
was saying that JOINs can only be done using '=', not BETWEEN.
Nevertheless, perhaps a SQL guru on this list can guide me on this
task.
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation https://edu.osgeo.org/
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
