MySQL Forums

Re: Points within polygons
Posted by: Bjorn Brala
Date: May 07, 2010 03:46AM

I think i did something similar to you, i use the bounding box of a map view to get items from within that view. But i use intersects.

WHERE Intersects(geofield, GeomFromText("Linestring('. \$this->fNElng .' ' . \$this->fNElat . ', ' . \$this->fSWlng . ' ' . \$this->fSWlat . ')"))

I use a linestring plotting a line from a northeast point to a southwest point, abusing the fact MySQL uses a MBR for its functions.

Also for clustering i used the following 'algorithm' slightly modified:

http://postgis.refractions.net/pipermail/postgis-users/2006-March/011430.html

I think its called a zcurve. In order to full the zcurvefield efficiently i convert lat and long to a binary value (padded with zero's on the left) and use that value for rouding, actually creating a sort of quad tree :)

PHP:

static public function binaryInterleave(\$sLat, \$sLon){
\$sLat = floatVal(\$sLat);
\$sLon = floatVal(\$sLon);
\$sLat = (\$sLat += 90) * 0.5;
\$sLon = (\$sLon += 180)* 0.3;

\$sLat = round(\$sLat * 100000000);
\$sLon = round(\$sLon * 100000000);

\$sLat = str_pad(\$sLat, 10, "0", STR_PAD_LEFT);
\$sLon = str_pad(\$sLon, 10, "0", STR_PAD_LEFT);

\$sLat = base_convert(\$sLat, 10, 2);
\$sLon = base_convert(\$sLon, 10, 2);

\$aLength = array(strlen(\$sLat), strlen(\$sLon));
\$iMaxLength = max(\$aLength);

\$sLatConverted = str_pad(\$sLat, 36, "0", STR_PAD_LEFT);
\$sLonConverted = str_pad(\$sLon, 36, "0", STR_PAD_LEFT);

\$sReturn = '';
for(\$i=0;\$i<\$iMaxLength;\$i++){
if(\$sLonConverted{\$i} == '.' || \$sLatConverted{\$i} == '.'){
\$sReturn .= '.';
continue;
}

\$sReturn .= \$sLonConverted{\$i};
\$sReturn .= \$sLatConverted{\$i};
}
\$sReturn = str_replace('..', '.', \$sReturn);

return \$sReturn;
}

By rounding the zcurve column ( varchar(255), indexed ) you can create clusters of points exceptionally fast.

"GROUP BY SUBSTRING(zcurve, 1, ". \$this->getRoundAmount() . ")";

You can play around with rounding until you find a nice setting for the zoom level.

Options: ReplyQuote

Subject
Views
Written By
Posted
7902
February 18, 2010 06:50PM
3210
March 27, 2010 04:29AM
4234
April 09, 2010 01:33AM
3042
April 09, 2010 01:50AM
5320
April 30, 2010 04:43PM
3209
April 30, 2010 06:48PM
2782
April 30, 2010 09:38PM
4219
May 03, 2010 05:33AM
3372
May 06, 2010 08:56AM
Re: Points within polygons
4647
May 07, 2010 03:46AM

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.