Spatial Database - how to

I have some data stored as ..
Name
Add
latitude
longitude

I've thought of implementing spatial indexing instead of normal indexing. As a newbie i'd like to know whether this is a good idea or not.And also some steps to implement database and basic searches.
What i did till now is:

CREATE TABLE `geo` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(40) NOT NULL,
  `loc` point NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

INSERT INTO geo VALUES('some-name', GeomFromText('POINT(06.03276 -15.046306)'));

Now in such data
How can i do a search for particular location based on lat/long?
Will such search will be faster than normal indexing?
Please provide me with some best practices / ideas / implementations / sample spatial database....

Thanks.
LVL 21
K VDatabase ConsultantAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
fiboConnect With a Mentor Commented:
Using the spatial extensions is great for SOME topological problems.
The most-frequently found example is to test if a given point is or is not inside a polygon, a problem otherwise difficult to solve without extensive mathematics.

So you could use it to find points within a given rectangle.

Not sure in that case how speed would compare with a search for "( long BETWEEN longmin AND longmax) AND (lat BETWEEN latmin AND latmax)": in this special case of a rectangle, the topological problem is simple and I would expect the traditional search to be faster.
0
 
fiboCommented:
Hmmm... May I suggest some changes?
Since you are going to search along coordinates, I would use not "GeomFromText" but directly longitude and latitude,  and index these 2 fields
0
 
K VDatabase ConsultantAuthor Commented:
Hi fibo, i was expecting atleast one rely...
well as i said, we already have lat-long indexed, but now i'm trying to implement the same using the
"spatial"  facility provided by mysql.
I want  to know how can i query to that table for some lat-long between given range?
And will it be faster / slower than regular indexes?
Thanks again.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
faridurCommented:
It is always faster to search and do spatial queries if you do GIST Indexing on your geometry column. The sample code in PostgreSQL (with PostGIS) is given below where the table name is "tblfaults" and the geometry column is "the_geom"

CREATE INDEX tblfaults_idx ON tblfaults USING gist(the_geom);
ALTER TABLE tblfaults CLUSTER ON tblfaults_idx;

Open in new window

0
 
K VDatabase ConsultantAuthor Commented:
I'm a bit confused about querying to this 'loc' field.
Lets say, I've got a set of lat-long between which i need to find the points.
Say a polygon with 3 points, and i need all data falling in that triangle. How can i query that on such geo-spatial data?
What i used to do is the '(xxx>= and xx<=) sub queries.
0
 
faridurConnect With a Mentor Commented:
In your case you'll need to do a "Intersection" Spatial Analysis between the two geometries. For this, the set of lat-lon points should be stored as a geometry and not as individual columns in the db. Then you will have to do a point in polygon analysis to find out what all points intersect the polygon (the triangle you mentioned). The query will be something like the one shown below. Here we are  finding all Users [Users.name (Point-Geometry)] within 'India' (Polygon-Geometry)"
SELECT user.name, user.the_geom
 FROM user,
      (SELECT the_geom
       FROM Country
       WHERE Country.name = 'India') as country
 WHERE
   within( user.the_geom, country.the_geom )
 AND
    the_geom && country.the_geom

Open in new window

0
All Courses

From novice to tech pro — start learning today.