Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have Point of Interest data in MySQL. This are the fields

latitude,longitude,address,types

in which I have stored the data. Now i want to import it into PostGIS Database. But does it required geometry of this point??

which command is to be used to find nearest point with radius or distance ??

Thank you

share|improve this question
add comment

2 Answers

Bring your data into PostgreSQL with something like mysql_fdw, or other methods.

Once you have this table, add a geography column, and populate the new column:

ALTER TABLE places ADD COLUMN geog geography(Point,4326);
UPDATE places SET geog = ST_MakePoint(longitude, latitude);

Now select the nearest 10 places that are within 100 kms:

SELECT places.*, ST_Distance(geog, poi)/1000 AS distance_km
FROM places,
  (select ST_MakePoint(-90,47)::geography as poi) as poi
WHERE ST_DWithin(geog, poi, 100000)
ORDER BY ST_Distance(geog, poi)
LIMIT 10;
share|improve this answer
    
100 Kms is nothing but the radius ? Can i restrict the result with types ?? –  sandeep Nov 16 '12 at 18:02
1  
sure, just modify the WHERE part of the query. Also yes, ST_DWithin is a filter that uses a custom radius in metres. –  Mike T Nov 17 '12 at 4:19
    
How can i alter the table fields "latitude,longitude" to UNIQUE ? –  sandeep Nov 17 '12 at 16:37
1  
These are CONSTRAINTS: ALTER TABLE places ADD CONSTRAINT places_lat_lon_unique UNIQUE(latitude,longitude); –  Mike T Nov 17 '12 at 20:09
    
Thanks 1 more question: we use this 'SELECT address From places WHERE types LIKE "%university%"' MySQL Command when in "types" field there are other things also. Like in "types" field there are other data "building,school,university,school_building" with comma separated. I don't know which command is used in PostgreSQL in such type of situation? –  sandeep Nov 18 '12 at 9:42
show 1 more comment

Sandeep,

You may not absolutely need to create geometries, but you will want to do it so that you can take advantage of indexes. The below code works with Postgres 9.1+

 CREATE TABLE mypoints(gid SERIAL PRIMARY KEY, latitude double precision, longitude double precision, address varchar(100), types varchar (25), geom geometry(POINT, 4326));

 INSERT INTO mypoints (latitude, longitude, address, types, geom)VALUES (40.0,-90.0, '123 Main', 'house', ST_GEOMFROMTEXT('POINT(-90.0 40.0)',4326))
 INSERT INTO mypoints (latitude, longitude, address, types, geom)VALUES (41.0,-90.0, '123 Smith', 'house', ST_GEOMFROMTEXT('POINT(-90.0 41.0)',4326))
 INSERT INTO mypoints (latitude, longitude, address, types, geom)VALUES (42.0,-90.0, '123 Johnson', 'house', ST_GEOMFROMTEXT('POINT(-90.0 42.0)',4326))
 INSERT INTO mypoints (latitude, longitude, address, types, geom)VALUES (43.0,-90.0, '123 Oak', 'warehouse', ST_GEOMFROMTEXT('POINT(-90.0 43.0)',4326))
 INSERT INTO mypoints (latitude, longitude, address, types, geom)VALUES (44.0,-90.0, '123 Pine', 'store', ST_GEOMFROMTEXT('POINT(-90.0 44.0)',4326))
 INSERT INTO mypoints (latitude, longitude, address, types, geom)VALUES (45.0,-90.0, '123 Granite', 'factory', ST_GEOMFROMTEXT('POINT(-90.0 45.0)',4326))

  SELECT latitude, longitude, address, types
    FROM  mypoints
ORDER BY geom <-> st_setsrid(st_makepoint(-90,47),4326)
 LIMIT 1;

If you are looking for all of the features/points within a certain distance, you could use St_DWithin()

share|improve this answer
1  
Unfortunately, you shouldn't use simple distance operators on unprojected coordinates. Is 'POINT(-92.1 47.0)' closer or further to the POI than "123 Granite"? Although it's "Cartesian" distance is 2.1 (slightly further), it's distance on the spheroid makes it a closer point. –  Mike T Nov 16 '12 at 2:45
    
Good point Mike. It would make sense to convert to a SRS that is true for distance or use the PostGIs geography type. The above method would still work in a SRS like one of the UTM Zones. –  DavidF Nov 16 '12 at 4:17
1  
@DaveF yes the '<->' operator works wonderfully in a UTM zone. –  Mike T Nov 16 '12 at 5:36
    
Thanks @DavidF. Can you tell me how can i Alter the table to change the Latitude and Longitude as Unique Key?? So that i can avoid to add the same co-ordinate again –  sandeep Nov 16 '12 at 9:32
1  
The SQL is pretty much the same: WHERE types = 'university'. This would be an exact string match, and more efficient than a LIKE with double wildcards. –  DavidF Nov 16 '12 at 20:20
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.