Spatial Postgres-GRASS queries examples

This article complete in PDF format as presented to the GRASS GIS 2002 users conference, Trento, September 2002 here.
-------------------------

Simple spatial queries that can be made with PostgreSQL internal type 'polygon' to GRASS vector area map (after importing this map
to PostgreSQL with v.to.pg, pictures made with d.vect.pg -s map=kuruma_id sql=<test_i.sql> color=red):

1. Ring

2. Stripes

select rec_id from info_kuruma_bnd where ((boundary::box <-> point ('(600000,
4984000)')) < 10000 and (boundary::box <-> point ('(600000,
4984000)')) > 5000) select rec_id from info_kuruma_bnd where
(boundary::box << point ('(620000, 4984000)') and
boundary::box >> point ('(610000, 4984000)')) or
(boundary::box << point ('(600000, 4984000)') and
boundary::box >> point ('(590000, 4984000)')) or
(boundary::box << point ('(580000, 4984000)') and
boundary::box >> point ('(570000, 4984000)'))

3. Adidas stripes (skewed by 30 dg.)

4. Sector 30 dg. from point (590000, 4980000)

select rec_id from info_kuruma_bnd where (boundary::box * point ('(.866,.5)')<< point ('(620000, 4984000)') * point ('(.866,.5)') and boundary::box * point ('(.866,.5)')>> point ('(610000, 4984000)') * point ('(.866,.5)')) or (boundary::box * point ('(.866,.5)')<< point ('(600000, 4984000)') * point ('(.866,.5)') and boundary::box * point ('(.866,.5)')>> point ('(590000, 4984000)') * point ('(.866,.5)')) or (boundary::box * point ('(.866,.5)')<< point ('(580000, 4984000)')* point ('(.866,.5)') and boundary::box * point ('(.866,.5)')>> point ('(570000, 4984000)') * point ('(.866,.5)')) select rec_id from info_kuruma_bnd where (boundary::box * point ('(.866,.5)')>> point ('(590000, 4980000)') * point ('(.866,.5)') and boundary::box * point ('(.5,.866)')<< point ('(590000, 4980000)') * point ('(.5,.866)'))

5. Moving shadow

select rec_id from info_kuruma_bnd where ((boundary && new_inf.boundary) and new_inf.rec_id = 1) or ((boundary && polygon(new_inf.boundary::box + point('(-10000,0)'))) and new_inf.rec_id = 1) or ((boundary && polygon(new_inf.boundary::box + point('(-20000,5000)'))) and new_inf.rec_id = 1) or ((boundary && polygon(new_inf.boundary::box + point('(-30000,10000)'))) and new_inf.rec_id = 1) or ((boundary && polygon(new_inf.boundary::box + point('(-40000,15000)'))) and new_inf.rec_id = 1) etc, ...

Alex Shevlakov,
IS consultant, Moscow, Russia,
sixote@yahoo.com

1. Ring	2. Stripes
select rec_id from info_kuruma_bnd where ((boundary::box <-> point ('(600000, 4984000)')) < 10000 and (boundary::box <-> point ('(600000, 4984000)')) > 5000)	select rec_id from info_kuruma_bnd where (boundary::box << point ('(620000, 4984000)') and boundary::box >> point ('(610000, 4984000)')) or (boundary::box << point ('(600000, 4984000)') and boundary::box >> point ('(590000, 4984000)')) or (boundary::box << point ('(580000, 4984000)') and boundary::box >> point ('(570000, 4984000)'))
3. Adidas stripes (skewed by 30 dg.)	4. Sector 30 dg. from point (590000, 4980000)
select rec_id from info_kuruma_bnd where (boundary::box * point ('(.866,.5)')<< point ('(620000, 4984000)') * point ('(.866,.5)') and boundary::box * point ('(.866,.5)')>> point ('(610000, 4984000)') * point ('(.866,.5)')) or (boundary::box * point ('(.866,.5)')<< point ('(600000, 4984000)') * point ('(.866,.5)') and boundary::box * point ('(.866,.5)')>> point ('(590000, 4984000)') * point ('(.866,.5)')) or (boundary::box * point ('(.866,.5)')<< point ('(580000, 4984000)')* point ('(.866,.5)') and boundary::box * point ('(.866,.5)')>> point ('(570000, 4984000)') * point ('(.866,.5)'))	select rec_id from info_kuruma_bnd where (boundary::box * point ('(.866,.5)')>> point ('(590000, 4980000)') * point ('(.866,.5)') and boundary::box * point ('(.5,.866)')<< point ('(590000, 4980000)') * point ('(.5,.866)'))

5. Moving shadow
select rec_id from info_kuruma_bnd where ((boundary && new_inf.boundary) and new_inf.rec_id = 1) or ((boundary && polygon(new_inf.boundary::box + point('(-10000,0)'))) and new_inf.rec_id = 1) or ((boundary && polygon(new_inf.boundary::box + point('(-20000,5000)'))) and new_inf.rec_id = 1) or ((boundary && polygon(new_inf.boundary::box + point('(-30000,10000)'))) and new_inf.rec_id = 1) or ((boundary && polygon(new_inf.boundary::box + point('(-40000,15000)'))) and new_inf.rec_id = 1)	etc, ...