SQL syntax for selecting points within a Polygon

[FreeLunch]

Thanks in advance for your help. I understand SQL, but I'm new to SQL
within MapInfo. I have two tab files. Tab file one, say Polygon.tab,
contains polygons representing areas on a map. Tab file two, say
points.tab, contains points on the map.

I want to write a query that sums up a field in the points table by
polygon objects. Attached below is my "pseudo" SQL explaining what
I'm trying to do. Obviously that syntax is not right, but hopefully
it will explain my intent.

Select Polygon.ID, sum(points.Value1) where points within polygon from
Polygon, Points

[lesliecn]

Hi FreeLunch ,

the below code is a example to get point or region that contains
each others
First the pointtab hasone field ColP,and regiontab has two fields
ColR1,ColR1 ....

'1、Get all the region
select * from RegionTab where RegionTab.obj contains any (Select obj
from PointTab) into temp
Note "Get region"

'2、Select all the regions Contain points
select * from PointTab,RegionTab where PointTab.obj within
RegionTab.obj

'3、Group by the Region
Note "Get point"
select ColR1,count(*),ColR2 from temp group by ColR1 into temp2

'4、Get the region that caontain 2 point
Select * from RegionTab where ColR2 in (select col3 from temp2 where
col2=2)
Note "Get region contain 2 point"

[lesliecn]

well i had uploaded the source code and example to the mapinfo-l ,..
the link is
http://www.mapinfo-l.com/Members/jacky/mapbasic_point_region.rar/download

[FreeLunch]

Lesliecn,

Thank you so much for your help and great example. It wasn't exactly
what I needed, but I was able to use the syntax and adjust it to what
I needed it to do. Below are the 2 sql statements that I am using to
accomplish my task.
Thanks again for your help.


Select Region.Name, Sum(Point.sales),
Sum(Point.Inventory),Sum(Point.Inventory2) from Region,Point where
Region.Obj Contains Point.Obj group by Region.Name into Temp1

Select COL1, (COL3 + COL4)/COL2*12"Months2SoldOut" from Temp1 group by
COL1 into Months2SoldOut

On Mar 27, 9:45 pm, lesliecn <leilaa...@hotmail.com> wrote:
> well i had uploaded the source code and example to the mapinfo-l ,..
> the link is

>  http://www.mapinfo-l.com/Members/jacky/mapbasic_point_region.rar/down...

[lesliecn]

well ,the case is the "(COL3 + COL4)/COL2*12 "Months2SoldOut"" not a "
Group by" condition, so u can combine the 2 sql to 1 such as :

Select Region.Name, (Sum(Point.Inventory)+Sum(Point.Inventory2))/
Sum(Point.sales)*12 "Months2SoldOut" from Region,Point where
Region.Obj Contains Point.Obj group by Region.Name into Months2SoldOut

[alaskamaps]

Hi, I think I'm trying to do the same thing in MapInfo... but I don't
know SQL. What I want should be simple... the total number of points
in each polygon. Would you mind explaining to me in plain english how
to obtain this? All the computer lingo is well over my head.

[geomatics.ch]

Hi,
The easiest way to explain this would be to show how to color the
polygons (from dark to light) depending on how many points are
included in each polygon.

For example,
if Polygon A contains 1 to 3 points, it would be colored light yellow
if Polygons B, C, D contain between 4 and 10 points, they would be
colored orange
and Polygon E contains between 11 and 20 points, it would be colored
red

If this is what you're trying to do, here's the step-by-step:

Have the Polygon and Points layer in the same Map Window
Menu Map>Create Thematic Map
1.
Choose Type: Ranges
Template Name: Region Ranges Default
2.
Select a Table and a Field
Table :Polygons (it's the Polygons you want to color)
Field: Join
3.
Get Value from table: Points
Join: Where Value from Polygons Contain Points (polygons contain
points or points are within a polygon)
Calculate: Count
OK
Next
4.coustomize ranges, styles and legend (you may read more about this
unde help)