Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Spatial Database - how to

Posted on 2008-10-16
7
Medium Priority
?
781 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:K V
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:K V
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 750 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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:K V
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 750 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

715 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