Solved

Spatial Database - how to

Posted on 2008-10-16
7
769 Views
Last Modified: 2008-12-04
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.
0
Comment
Question by:theGhost_k8
  • 2
  • 2
  • 2
7 Comments
 
LVL 29

Expert Comment

by:fibo
ID: 22742401
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
 
LVL 21

Author Comment

by:theGhost_k8
ID: 22747037
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
 
LVL 29

Accepted Solution

by:
fibo earned 250 total points
ID: 22748544
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 2

Expert Comment

by:faridur
ID: 22885153
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
 
LVL 21

Author Comment

by:theGhost_k8
ID: 22919171
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
 
LVL 2

Assisted Solution

by:faridur
faridur earned 250 total points
ID: 22923220
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now