Identifying points outside polygons

32 views
Skip to first unread message

scruss

unread,
Feb 16, 2010, 1:19:51 PM2/16/10
to SpatiaLite Users
[Apologies for the beginner's spatial SQL question. If there's a
better forum to ask, please let me know where it is, thanks.]

I have the following tables:
* Properties - a table containing land polygons, with a column
"Owner', containing the land owner's name.
* Points - a table containing locations, with a numeric column UnitID.

How do I query the point UnitIDs that are *not* within a property
polygon?

An illustration of this problem, as well as a tiny SpatiaLite database
containing the layers, is here: http://scruss.com/blog/2010/02/10/diary-of-a-geonumpty/

The example is trivial, but in real life I'm analysing several hundred
properties and points at once.

Many thanks,
Stewart

Harsha

unread,
Feb 16, 2010, 7:31:34 PM2/16/10
to SpatiaLite Users
Scruss,

I poked around a bit with the dataset from your blog and came up with
this
select id,unitid from points where id not in (select points.id from
points, properties where within(points.geometry, properties.geometry))

I'm sure there is a better way to do this but this served the purpose

-Harsha

scruss

unread,
Feb 16, 2010, 9:13:53 PM2/16/10
to SpatiaLite Users
On Feb 16, 7:31 pm, Harsha <harsha.ci...@gmail.com> wrote:
> Scruss,

>
> I'm sure there is a better way to do this but this served the purpose

Thanks, that's great - it's not a huge problem to be solved, so it's
not like the nested query will take forever.

Much appreciated. Now to try it on the real dataset ...

cheers,
Stewart

Reply all
Reply to author
Forward
0 new messages