0

I would like to improve my knowledge regrading this issue.

I am using PostgreSQL version 9.5 (x64bit) on Windows 7 Enterprise (x64bit Machine). I have a table containing 3515214 rows of X,Y coordinates and a geometry column. Every time I tried to import this table to my Postgres DB using GUI tool i.e., PostGIS Shapefile and DBF Loader 2.2, I found some missing x, y values in this table when I viewed the data in SQL editor using:

Select * from my_table;

I cross-checked for missing geometry values as well like this:

SELECT COUNT(*) FROM my_table WHERE geom IS NULL;

The count result was zero means no missing geometry values. However, when I checked for X coordinate:

SELECT COUNT(*) FROM my_table WHERE x IS NULL;

There were 89k+ missing x (and y) values. I am aware that I can use shp2pgsql() or ogr2ogr() tool as well but I am not sure how these will work for this large dataset. However, my point is: are there any limitations in using GUI tool while importing a large table? How may I tackle with this ambiguity?

  • 1
    Another workaround for testing : 1 > Assuming you have a SQL table called mydata - you can load data from a csv file as follows: COPY MYDATA FROM '<PATH>/MYDATA.CSV' CSV HEADER; 2> then update table creating geom with st_mapmakePoint() SELECT ST_SetSRID(ST_MakePoint(x_col, y_col),4326); MAy help debuguging possible issues with GUI's and the others tools , or the data – Jorge Vidinha Jul 28 '16 at 12:40
  • @JorgeVidinha: Thank you so much for your debugging solution. It is helpful indeed. Nevertheless, I was more interested to understand this ambiguity of GUI tool. – khajlk Jul 28 '16 at 14:26

Your Answer

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

Browse other questions tagged or ask your own question.