Using PostGIS with an existing data table that has decimal coordinates

Question

I have a data file in CSV format that has the some data like this: field id | data tile | data description | longitude | latitude

I have PostgreSQL and PostGIS already installed and I would like to use PostGIS to create a query that will bring up all the records (from the table above) that are within the specified distance from a lat/long location.

The problem is that I don't know how to get started. Do I just import my CSV file in PostgreSQL database and start using PostgreSQL and PostGIS functions once I have the CSV file converted into a PostgreSQL table?

Are there any extra steps to make the table work with the PostGIS functions?

What would be the correct steps to go about this? I truly appreciate your help!

Answer 1

In order to work with PostGIS, you need to import these scripts into your working database.

Once you have this step done, set up your table and add the PostGIS column to it

SELECT AddGeometryColumn('yourtable', 'columnname', 4269, 'POINT', 2 );

The last 3 values are SRID, the type and the dimension. I just assume this is what you want. You probably need to adjust the SRID but this should be fine.

You can set in your import/update/whatever you chose to add the geometry like

ST_Transform(ST_PointFromText('POINT(-85.45899 32.1337)', 4326));

This query can be written into your UPDATE or INSERT script for the data.

Answer 2

I just had to solve the same task and did something similar to DrColossos's solution... You're probably done with it by now, but I just wanted to add this in case someone googles this issue (like I did): There's no need to write a script to generate a geometry from the lat/lon fields, you can use a simple concatenated string:

UPDATE mytable
SET the_geom = ST_PointFromText('POINT(' || x ||' '|| y ||')', 4326)