Subscribe to this thread
Home - General / All posts - Spatial SQL Points to areas - Unexpected Token
Louise Semaj78 post(s)
#06-Jun-24 07:00

Some years ago I was helped here with a query to select points into areas. This was the sql (or similar). This simple query is amazing. Spatial SQL is so powerful.

Mostly I can eventually get it to work, but just sometimes I get messages and I dont seem to be able to get it to run

SELECT [Areas].[AreaName], [Points].[ID]

FROM [Grid 500] as Points,[Areas] as Areas

WHERE Contains([Areas].[ID],[Points].[ID]);

Likely I have bits wrong in the syntax. Presently I am getting Token Expected error. I have also attached an example map file that I am having difficulty in running.

I am wanting Point IDs to be allocated into the Areas.

I also read an earlier post that sometimes data types / size may cause something like this. Any assistance would be greatly appreciated.

Many thanks and kind regards

Attachments:
Tester Unexpected Token.map

Louise Semaj78 post(s)
#06-Jun-24 08:02

Apologies. Further. I am running Win 10 and Manifold 8.0.35.0

Dimitri


7,452 post(s)
#06-Jun-24 11:47

In Release 8 the Contains() function requires geoms as arguments, not numeric ID values. In Release 9 there is a GeomContains() function (not Contains), which also takes geoms as arguments (see the user manual).

It's not clear what you mean by "select points into areas". When you write "wanting point IDs to be allocated into the Areas" it sounds like you want to do a spatial JOIN. I do all of those in 9, not 8, one reason being the really super Join dialog in 9.

The easiest way to learn how to do a spatial join in 9 is to use the Edit - Join dialog to set up the join you want and then hit the Edit Query button so see the SQL it writes. If you're interested in joining a numeric ID field for points, then it seems you'd want to do a sum or some other aggregate operation. If you want a list of point IDs in each area, then you'd first create a text version of the ID field and join that, using the Join Tokens aggregate method.

If what you want to do is selections, then use the Select pane with spatial selection, and use the Edit Query button to see the SQL it writes.

Using the .map you attached, the query in 9 analogous to that in your post is:

SELECT [Areas].[AreaName][Grid 500].[ID]

FROM [Areas][Grid 500] 

WHERE GeomContains([Areas].[Geom (I)],[Grid 500].[Geom (I)], 0);

(I don't use alias quite so much since it is easier to get confused with similarly named aliases...) However, that doesn't look particularly useful, since it looks like you have overlapping areas where multiple areas contain the same points. I get the feeling there is a better way to do whatever is the end objective for your project.

Louise Semaj78 post(s)
#07-Jun-24 03:18

Thanks Dimitri, I do have V9 as well but still prefer to work in V8 where I can. Appreciate the links to the help files, they are always very comprehensive.

Perhaps I to try to explain my objective further... I am seeking a list of Area names with point ID enclosed in each. Note the Point ID's will be duplicated as the Areas overlap. Hopefully this is a little clearer. See also image. Very much appreciate the assistance here. Kind regards

Attachments:
Point ID's to areas.jpg

Dimitri


7,452 post(s)
#07-Jun-24 07:57

Thanks for the explanation... I understand now. That's what the query I posted will do in 9 using your example .map.

Louise Semaj78 post(s)
#07-Jun-24 10:28

Many thanks

Sloots

690 post(s)
#06-Jun-24 13:26

There is nothing wrong with your query. In Mfd8 the use of [ID] as a proxy for [Geom (I)] works fine.

The problem is that your code contains some invisible 'strange' characters. If I copy your code in NotePad+ and I switch the encoding to ANSI they appear as 'Â'. If you retype your code in a new query component it runs fine. Or you can remove all spaces in your code and then put new spaces back in place (including the two invisible spaces after the FROM clause).


http://www.mppng.nl/manifold/pointlabeler

Louise Semaj78 post(s)
#07-Jun-24 03:11

Wow - What an eye opener looking at it with the test editor. Thanks for the tip - I have retyped and now it works fine . Many thanks for your insight here. Kind regards

Louise

Dimitri


7,452 post(s)
#07-Jun-24 07:56

In Mfd8 the use of [ID] as a proxy for [Geom (I)] works fine.

You're right. Forgot about that!

Louise Semaj78 post(s)
#07-Jun-24 10:28

Great, Thanks

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.