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


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 ?

  • Can we remove the references to MySQL here? @polygeo? No one actually answered his original question? And it had nothing to do with PostGIS originally. To further confuse the matter MySQL now supports bounding box within and can do the same thing dev.mysql.com/doc/refman/8.0/en/… Jan 13, 2017 at 17:46
  • @EvanCarroll that's not something that I think we should do without testing community support first. With so many votes already on the question and its answers I think we should use a Meta Q&A to see what the community wants to do with it. Let me know if you want me to ask that Meta Q&A or would prefer to ask it yourself.
    – PolyGeo
    Jan 13, 2017 at 20:57
  • I don't intend on asking the community. I'd just as soon close it or remove the reference. Sandeep seemed to have moved to use PostGIS, so it's clearly a relic by the comments on his answers, and there isn't a single answer here that entertains MySQL. So we should either be downvoting all of them, or chang the question so they matter (only because of sandeep's cooperation in the answers with PostGIS as a solution.) Jan 13, 2017 at 21:01
  • Keep the word MYSQL there as it shows that data was in MYSQL database. Its moved from MySQL to POSTGIS. Question was how to move data from MySQL to POSTGIS and then find the nearest poi in PostGIS database. Its not confusing.
    – sandeep
    Jan 14, 2017 at 9:46

3 Answers 3


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)
  • 100 Kms is nothing but the radius ? Can i restrict the result with types ??
    – sandeep
    Nov 16, 2012 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, 2012 at 4:19
  • How can i alter the table fields "latitude,longitude" to UNIQUE ?
    – sandeep
    Nov 17, 2012 at 16:37
  • 1
    These are CONSTRAINTS: ALTER TABLE places ADD CONSTRAINT places_lat_lon_unique UNIQUE(latitude,longitude);
    – Mike T
    Nov 17, 2012 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, 2012 at 9:42

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)

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

  • 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, 2012 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, 2012 at 4:17
  • 1
    @DaveF yes the '<->' operator works wonderfully in a UTM zone.
    – Mike T
    Nov 16, 2012 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, 2012 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, 2012 at 20:20

Use postgresql as database, and install postgis extension, and enable the extension for your database.

Then use a column similar to location GEOGRAPHY(POINT,4326) to store location.

Create a index for the column using GIST index type, similar as:

CREATE INDEX global_points_gix ON global_points USING GIST ( location );

Insert similar as:

INSERT INTO global_points (name, location)
VALUES ('Town', ST_GeographyFromText('SRID=4326;POINT(-110 30)') );

Query points within distance range similar as:

SELECT name FROM global_points
WHERE ST_DWithin(location, ST_GeographyFromText('SRID=4326;POINT(-110 29)'), 1000000);

More details refer to:


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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