1

I am looping through a csv, taking latlng values and trying to add there geometry to postgis enabled postgres table. xy values are int's

conn = psycopg2.connect("connected")
cur = conn.cursor()
cur.execute('''CREATE TABLE test pt_geom geometry);''')
conn.commit()
for row in csvfile:#sample code
    x = row[0]
    y = row[1]#etc...
    xy = (x, y)#tuple of the int xy's
    cur.execute('''insert into test
                        (pt_geom) values
                        (ST_SetSRID(ST_MakePoint(%s,%s),4326));''', (xy))
    conn.commit()

the script is running, connecting to the db, creating the table inserting the latlng but the values are coming up in a strange text format-I am not sure why that is..I have not found any info on this yet on the web. possible solutions? readsoning? the xy is an int value. is it spatially displayed? does the %s convert it to a string...anything would be helpful

enter image description here

2 Answers 2

2

The result is in geometry format. You could see the coordinates of that point by adding st_astext(geom) function while selecting.

select st_astext(geometry_column) from table_name;
1

This is WKB format wichs is a binary representation of the geometry. To select the geometry in GeoJSON format for example you can use : ST_AsGeoJSON()

8
  • so the geometry xy locations are good?
    – ziggy
    May 13, 2016 at 14:16
  • There's no reason to think are bad, use the function I say in my edited answer and check if the point are the ones you tried to insert from your CSV. May 13, 2016 at 14:18
  • do i use ST_AsGeoJSON to create the table or when i use the insert method?
    – ziggy
    May 13, 2016 at 14:26
  • No, you should use in SELECT query to select the point of the table in this format. The "geometry" data type stores the geometry in WKB, so that is what you see when you use SELECT pt_geom from test. What i'm suggesting you is SELECT ST_AsGeoJSON(pt_geom) FROM test. May 13, 2016 at 14:37
  • 1
    Yes, you can have more than one geometry column and giving indexes and contraints for each geometry colum. =) May 13, 2016 at 14:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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