Another SQL Query question

davidhhardman

unread,

Oct 16, 2007, 4:22:09 AM10/16/07

to MapInfo-L

I have two tables, one contains lines and the other contains points.

I need a SQL select query to select all of the points that are within
1 metre of any of the lines. It seems simple but I can't get it to
work - can anyone help

Thank you
Dave

Driver, Greg 9434

unread,

Oct 16, 2007, 4:32:09 AM10/16/07

to mapi...@googlegroups.com

Dave,

You need to create a temporary buffer around you're lines and then
select the points within these:

Select * from points where obj within any (select buffer(obj, 12, 1,
"m") from lines) into selection

HTH

Greg Driver

System Administrator
Applications Support
ICT
NOT PROTECTIVELY MARKED

Peter Horsbøll Møller

unread,

Oct 16, 2007, 4:36:00 AM10/16/07

to mapi...@googlegroups.com

Try:

Select *
From POINTS
Where OBJ Within Any (Select CartesianBuffer(OBJ, 24, 1, "m") From LINES)

Be aware that your points are selected as many times as there are within the line buffers.
So if a point is within 2 buffers, it will appear twice in the result.

You can avoid this if you ahve an ID you can group by

Peter Horsbøll Møller
GIS Developer, MTM GeoInformatics
Geographical Information & IT

COWI A/S
Odensevej 95
DK-5260 Odense S.
Denmark

Tel     +45 6311 4900
Direct +45 6311 4908
Mob     +45 5156 1045
Fax     +45 6311 4949
E-mail p...@cowi.dk
http://www.cowi.dk/gis

-----Original Message-----
From: mapi...@googlegroups.com [mailto:mapi...@googlegroups.com] On Behalf Of davidhhardman
Sent: Tuesday, October 16, 2007 10:22 AM
To: MapInfo-L
Subject: [MI-L] Another SQL Query question

davidhhardman

unread,

Oct 16, 2007, 4:45:26 AM10/16/07

to MapInfo-L

Again, spot on Thank you.

Is there a site where things like this are explained? I have a User
Guide here for MapInfo but it doesnt seem to tell me things like this.

Cheers

On Oct 16, 9:32 am, "Driver, Greg 9434"

> Dave- Hide quoted text -
>
> - Show quoted text -

Driver, Greg 9434

unread,

Oct 16, 2007, 5:01:31 AM10/16/07

to mapi...@googlegroups.com

Dave,

There's quite a good section in the back of the MI User Guide (Appendix
D in the v7.0 User Guide) on creating expressions that can be used
within SQL statements, though it doesn't cover everything. I've also
attached a short document that covers some 'advanced' SQL statements as
well. Failing that, you best bet is to keep posting on MI-L!

Regards

AdvSQLHandout - Tom Probert, MapInfo.zip

davidhhardman

unread,

Oct 16, 2007, 5:22:48 AM10/16/07

to MapInfo-L

I had no idea about the Appendices! That exact book has been on my
desk now for a while and I never thought to look in them. I shall read
it now! Thanks for the attachment as well. I do find forums like this
very helpful because at least you can ask if you don't understand
something, unlike a book!

On Oct 16, 10:01 am, "Driver, Greg 9434"

> AdvSQLHandout - Tom Probert, MapInfo.zip
> 551KDownload- Hide quoted text -

Rob Schneider - Aurchem Exploration

unread,

Oct 19, 2007, 1:33:45 PM10/19/07

to mapi...@googlegroups.com

I have an SQL question also - is there a way to do this (pseudo code) in MI SQL:

Select samples.UtmE, samples.UtmN, assays.* from assays, samples where assays.SId = samples.SId
Ie. Use a '*' for only one of the 2 tables?

Also re:Users Guide - These appendices seem to have been removed from the latest guide. In older versions there were 2 guides (abridged and unabridged) but there only seems to be the abridged now for V9 (checked both my CD and Online and they both match the printed manual)

davidhhardman wrote:

Lars I. Nielsen (GisPro)

unread,

Oct 20, 2007, 7:28:52 AM10/20/07

to mapi...@googlegroups.com

Hi Rob,

Speaking for pre-v9 versions, the target *tablename.** isn't available
in MapInfo SQL, only * by itself.

Although it would be a welcome addition, and supposedly rather simple to
add, it hasn't been yet.