![]() |
Recipe #14 |
2011 January 28 |
Previous Slide | Table of Contents | Next Slide |
The problem Do you remember ?
|
PopulatedPlaceId | PopulatedPlaceName | LocalCouncilId | LocalCouncilName | County | Region |
... | ... | ... | ... | ... | ... |
12383 | Acitrezza | NULL | NULL | NULL | NULL |
12384 | Lavinio | NULL | NULL | NULL | NULL |
11327 | Altino | 69001 | ALTINO | CHIETI | ABRUZZO |
11265 | Archi | 69002 | ARCHI | CHIETI | ABRUZZO |
11247 | Ari | 69003 | ARI | CHIETI | ABRUZZO |
... | ... | ... | ... | ... | ... |
SELECT pp.id AS PopulatedPlaceId, pp.name AS PopulatedPlaceName, lc.lc_id AS LocalCouncilId, lc.lc_name AS LocalCouncilName FROM populated_places AS pp, local_councils AS lc WHERE ST_Contains(lc.geometry, Transform(pp.geometry, 23032)); |
SELECT pp.id AS PopulatedPlaceId, pp.name AS PopulatedPlaceName, lc.lc_id AS LocalCouncilId, lc.lc_name AS LocalCouncilName FROM populated_places AS pp, local_councils AS lc WHERE ST_Contains(lc.geometry, Transform(pp.geometry, 23032)) AND lc.lc_id IN ( SELECT pkid FROM idx_local_councils_geometry WHERE pkid MATCH RTreeIntersects( MbrMinX( Transform(pp.geometry, 23032)), MbrMinY( Transform(pp.geometry, 23032)), MbrMaxX( Transform(pp.geometry, 23032)), MbrMaxY( Transform(pp.geometry, 23032))); |
SELECT pp.id AS PopulatedPlaceId, pp.name AS PopulatedPlaceName, lc.lc_id AS LocalCouncilId, lc.lc_name AS LocalCouncilName, c.county_name AS County, r.region_name AS Region FROM populated_places AS pp LEFT JOIN local_councils AS lc ON (ST_Contains(lc.geometry, Transform(pp.geometry, 23032)) AND lc.lc_id IN ( SELECT pkid FROM idx_local_councils_geometry WHERE pkid MATCH RTreeIntersects( MbrMinX( Transform(pp.geometry, 23032)), MbrMinY( Transform(pp.geometry, 23032)), MbrMaxX( Transform(pp.geometry, 23032)), MbrMaxY( Transform(pp.geometry, 23032))))) LEFT JOIN counties AS c ON (c.county_id = lc.county_id) LEFT JOIN regions AS r ON (r.region_id = c.region_id) ORDER BY 6, 5, 4; |
Previous Slide | Table of Contents | Next Slide |
![]() | Author: Alessandro Furieri a.furieri@lqt.it |
This work is licensed under the Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0) license. | |
![]() |
Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation; with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts. |