Spatial data and mysql

Spatial data and mysql

am 25.04.2008 01:06:53 von Rob Wultsch

I have been storing points in mysql without use of the spatial
extension. I do not forsee the need to ever store more than points,
and am wondering if the spatial extensions would offer any significant
advantages. I have looked a bit for tutorials, etc... and have not
found much.

One feature that I would like is to be able to find all points withen
X distance from of point Y, without doing a table scan. Would the
spatial index (Rtree) be able to achieve this?

Are there any good tutorials (or heaven forbid, books) that anyone can suggest?

Should I go hang out with the cool kids that are using postGIS ;)

--
Rob Wultsch
wultsch@gmail.com
wultsch (aim)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Spatial data and mysql

am 25.04.2008 09:27:27 von 2wsxdr5

I just did a quick look at the documentation on the mysql spatial
extension and it seems like over kill for what you are looking for. An
easy way to approximate the search for all points a given distance from
another is to simply use a bounding box. An index on the X and Y
coordinates of the point then will make the search fast. something like
this...


SELECT *
FROM points
WHERE x >= minx AND x <= maxx AND y >= miny AND y <= maxy

If your data is evenly distributed in the space about 21% of the
returned points will be outside the distance you want. You can then use
a script to scan the result to find and reject the points you don't
want. I do this for a mapping project I have on a web site. I don't
have a lot of data so I can't say how well the performance is. Even if
you have a huge data set, as long as your result sets weren't too big,
this should be pretty fast.

If the points you are dealing with are latitude longitude coordinates, I
have the formula you need to calculate the distance written in PHP if
you want it.

Rob Wultsch wrote:
> I have been storing points in mysql without use of the spatial
> extension. I do not forsee the need to ever store more than points,
> and am wondering if the spatial extensions would offer any significant
> advantages. I have looked a bit for tutorials, etc... and have not
> found much.
>
> One feature that I would like is to be able to find all points withen
> X distance from of point Y, without doing a table scan. Would the
> spatial index (Rtree) be able to achieve this?
>
> Are there any good tutorials (or heaven forbid, books) that anyone can suggest?
>
> Should I go hang out with the cool kids that are using postGIS ;)
>
>

--
Chris W
KE5GIX

"Protect your digital freedom and privacy, eliminate DRM,
learn more at http://www.defectivebydesign.org/what_is_drm"

Ham Radio Repeater Database.
http://hrrdb.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Spatial data and mysql

am 25.04.2008 17:52:31 von Rob Wultsch

On Fri, Apr 25, 2008 at 12:27 AM, Chris W <2wsxdr5@cox.net> wrote:
> I just did a quick look at the documentation on the mysql spatial extension
> and it seems like over kill for what you are looking for. An easy way to
> approximate the search for all points a given distance from another is to
> simply use a bounding box. An index on the X and Y coordinates of the point
> then will make the search fast. something like this...
>
> SELECT *
> FROM points
> WHERE x >= minx AND x <= maxx AND y >= miny AND y <= maxy

Thank you for your response. While I rather dislike using hacks the
above would probably work well enough for my purposes.

Unfortunately I do most of my work in a version of MySQL <5.0
(3.23.49) so I don't have access to the index merge optimization so
the above would only use one index for me, but that would probably be
good enough. One more reason to try to get my sys admin to upgrade...

--
Rob Wultsch
wultsch@gmail.com
wultsch (aim)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Spatial data and mysql

am 24.06.2008 22:09:02 von Rob Wultsch

On Thu, Apr 24, 2008 at 4:06 PM, Rob Wultsch wrote:
> I have been storing points in mysql without use of the spatial
> extension. I do not forsee the need to ever store more than points,
> and am wondering if the spatial extensions would offer any significant
> advantages. I have looked a bit for tutorials, etc... and have not
> found much.
>
> One feature that I would like is to be able to find all points withen
> X distance from of point Y, without doing a table scan. Would the
> spatial index (Rtree) be able to achieve this?
>
> Are there any good tutorials (or heaven forbid, books) that anyone can suggest?
>
> Should I go hang out with the cool kids that are using postGIS ;)

I'm going to answer my own question for the sake of the archive.

The spatial index would be able to do an indexed search for points
within a bounded box on releases as early as 4.1. Take a look at
http://dev.mysql.com/doc/refman/4.1/en/using-a-spatial-index .html .

--
Rob Wultsch
wultsch@gmail.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org