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)

Answer 3

This is a fun question :-)

Do you know what coordinate system the lat lons are in?

I ask this question because it is relevant to how you store the data.

What you'll need to do is create a column in addition to the ones in the CSV file that contain a PostGIS geometry object of type POINT.

I would then import the CSV data into the standard columns in the table, then run a SQL update to construct the POINT data from each record and store that in the created geometry column.

At that point you should be able to use the PostGIS functions to access and predicate queries on the data.

This is all kind of meta what I'm saying above because there are intricacies with regard to the data being used. You will need to know the spatial referencing system used with the data initially.