Solved

Spatial Database - how to

Posted on 2008-10-16
7
774 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Latency in .net app using DB in .net 21 66
powershell code to list items in dropdown 6 66
Generate Scripts of Schema/Data with "WHERE" clause 6 64
form builder not starting 3 39
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Creating and Managing Databases with phpMyAdmin in cPanel.
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
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…

831 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