Spatial Database - how to

I have some data stored as ..

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:

  `id` int(11) NOT NULL auto_increment,
  `name` varchar(40) NOT NULL,
  `loc` point NOT NULL,
  PRIMARY KEY  (`id`)

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....

LVL 22
theGhost_k8Database ConsultantAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Bernard S.Connect With a Mentor CTOCommented:
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.
Bernard S.CTOCommented:
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
theGhost_k8Database 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.
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

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

theGhost_k8Database 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.
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 [ (Point-Geometry)] within 'India' (Polygon-Geometry)"
SELECT, user.the_geom
 FROM user,
      (SELECT the_geom
       FROM Country
       WHERE = 'India') as country
   within( user.the_geom, country.the_geom )
    the_geom && country.the_geom

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.