MySQL Forums
Forum List  »  GIS

Point in Polygon woes
Posted by: Simon Giddings
Date: May 28, 2013 09:01AM

My server is on a shared host which is running MySql version 5.5.30-30.2 - Percona Server (GPL), Release rel30.2, Revision 509 (taken from phpmyadmin).

I have a table containing 10,023 records defined like this
CREATE TABLE IF NOT EXISTS `poi` (
`id_poi` INT NOT NULL AUTO_INCREMENT ,
`Latitude` DECIMAL(20,17) NOT NULL ,
`Longitude` DECIMAL(20,17) NOT NULL ,
`LatLng` GEOMETRY NOT NULL ,
`Name` VARCHAR(80) NOT NULL ,
...
PRIMARY KEY (`id_poi`) ,
SPATIAL INDEX `position_index` (`LatLng` ASC) )
ENGINE = MyISAM

I have implemented a PointInPolygon user defined function which I found here (http://forums.mysql.com/read.php?23,286574,286574) and call it within a stored procedure.

This stored procedure takes a polygon (representing a buffer around an itinerary) and, should, return records which are contained within the polygon.

The stored procedure does a "simple" select, joining to other tables for output -
select p.id_poi, p.Latitude, p.Longitude, p.Name, p.Adresse1, p.Adresse2, p.CodePostale, p.Commune,
p.contract_type, p.contract_active, p.position_aprox, p.media_dir,
p.ot_managed, (select id_poi_ot from poi_ot_managed as ot where p.id_poi=ot.id_poi) as ot_ref,
(select if(isnull(Name), null, concat(Name, "<br/>", Commune)) from poi as ot join poi_ot_managed as otm on ot.id_poi=otm.id_poi_ot where otm.id_poi=p.id_poi) as ot_structure,
group_concat(concat(g.Name, ' - ', c.Name) order by h.principal desc, g.Name, c.id_poi_category separator ';') as Cats,
group_concat(concat(g.Name_en, ' - ', c.Name_en) order by h.principal desc, g.Name, c.id_poi_category separator ';') as Cats_en,
group_concat(c.Marker_basic order by h.principal desc, g.Name, c.id_poi_category separator ';') as icons
from poi as p join poi_has_categories as h on p.id_poi = h.id_poi
join poi_category as c on c.id_poi_category=h.id_poi_category
join poi_cat_group as g on g.id_poi_cat_group=c.id_poi_cat_group
where MBRContains(poly,p.LatLng) and PointInPolygon(p.LatLng, poly)
group by p.id_poi
order by p.contract_type desc;

This works great until I try to do a large search - itinerary spanning all of France.
I send a polygon containing 2384 points.
The stored procedure takes 197 seconds (3 minutes and 17 seconds) = far too long.

Can anyone advise me as to how to optimise this / speed it up ?
At this time, my php script is being timed out by the apache server and I do not have any control over the apache installation.

I hope someone will be able to help here.

Options: ReplyQuote


Subject
Views
Written By
Posted
Point in Polygon woes
4429
May 28, 2013 09:01AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.