2

I have PostGIS table with some points and various attributes. Yesterday I added some more points to the table and now weird things are happening when I try and display layers built from these points.

If I construct a set of layers with a queries that select one 'classification' only the new data is displayed (these all have the same new classification) the other layer points do not appear on the map although they are in the attribute table.

If I load all the data into one layer then the new points don't appear (again they are in the attribute table).

The new data had a different origin -- I imported it from a csv file, all the other data came of GPS.

I have discovered that 'the_geom' for the two groups looks different:

'new':

      POINT(1768983 5947119)
      POINT(1769033 5947085)

original:

    POINT(1768982.09596461 5948263.32822992)
    POINT(1768558.70815704 5947816.48845243)

The difference is now obvious but what I would like to know is why this causes problems and how it came about so I can avoid this issue in future.

Is one is an integer representation and the other float?

For the record the original data was loaded off a GPS using into QGIS and saved as shapefile in CSR 2193 and then loaded into PostGIS using SPIT. The new data was imported from a CSV file using layer->"add delimited text layer" (the coordinates were already in 2193) and then imported into PostGIS using the database manager.

QGIS version    2.0.1-Dufour    QGIS code revision  f738351
Compiled against Qt 4.8.5   Running against Qt  4.8.5
Compiled against GDAL/OGR   1.10.1  Running against GDAL/OGR    1.10.1
Compiled against GEOS   3.4.2-CAPI-1.8.2    Running against GEOS    3.4.2-CAPI-1.8.2 r3921
PostgreSQL Client Version   9.2.4   SpatiaLite Version  4.1.1
QWT Version 6.0.2   PROJ.4 Version  480
QScintilla2 Version 2.7.2
3
  • An update on this. I just deleted the new data from the the table added that layer with no filtering and everything displayed as expected. I then added the 'new' data and then added the the table again as another layer. This time only those point in the new data were displayed. Weird. One thing I have noticed is that the_geom looks different for the two groups. I have edited the main question to add this data. Jan 27, 2014 at 6:16
  • 1
    SELECT ST_AsText(the_geom) would be easier to debug than WKB presentation. Jan 27, 2014 at 8:24
  • Firstly thanks for all the reports. It is starting to look as if it is something specific to my set up which is a Mac mini running Maverick (and yes I know that qgis is not officially supported on maverick). Jan 29, 2014 at 18:02

3 Answers 3

3

I believe the two sets of data have different coordinate reference systems. The coordinate values are very different, so you have data showing up in two different areas. You'll need to identify both coordinate reference systems first. After that, you could build two tables and convert one to the other's coordinate reference system, then merge them. Or convert the csv data before importing it to the table's coordinate reference system.

(including sample coordinate values in the question will let me or someone else improve this answer)

1
  • This was my first reaction having been there and done that several times before ;) but when I do a "zoom to layer" I still don't get anything displayed. What is weird is that the new data I added displays fine, it is the old data that I can't get to display! I think I will rebuild the table from scratch, sigh... Jan 26, 2014 at 7:52
2

HM, if I use the PostGIS way all 4 points are present ...

PostGreSQL/PostGIS:

create table geo (id serial);
select addgeometrycolumn('public','geo','geom',2193,'POINT',2);
insert into  geo(geom) values (ST_GeometryFromText('POINT(1768982.09596461  5948263.32822992)',2193));
insert into  geo(geom) values (ST_GeometryFromText('POINT(1768558.70815704 5947816.48845243)',2193));
insert into  geo(geom) values (ST_GeometryFromText('POINT(1768983 5947119)',2193));
insert into  geo(geom) values (ST_GeometryFromText('POINT(1769033 5947085)',2193));

RESULT:

select id, ST_AsText(geom) from geo;
                st_astext                 
------------------------------------------
POINT(1768982.09596461 5948263.32822992)
POINT(1768558.70815704 5947816.48845243)
POINT(1768983 5947119)
POINT(1769033 5947085)
(4 Zeilen)

And QGIS shows them...

enter image description here

1
  • Same here , no problems with 2193 CRS or 3857 Jan 28, 2014 at 11:59
1
+50

The new dataset is integer while the old is float.

For QGIS, it is no problem adding the points as delimited text file with those coordinates as WKT.

Seems like Postgis does not like mixed float and integer values as coordinates.

For future use, make sure the csv has float coordinate data by adding .0 to the coordinates if necessary.

Or try to transform the data to EPSG:4326 and see if all points get displayed. Coordinates will most likely be float then.

5
  • I havent got any problems with float vs int coordinates in PostGIs servers but MSSQL WKT parser doesn't handle them very well Jan 28, 2014 at 11:58
  • I tried editing the csv file to add .0 to all the coordinates and reloading it but the problem persists :( but I think I how why. The numbers in the csv file are CRS 2193 which are inherently integer so they may well be converted to by the import process. Jan 29, 2014 at 18:51
  • I tried converting all points in the table to 4326 using ST_TRANSFORM but got the error that there was no CRS associated with the first row. I believe that this is the key. I at one stage while dumping out the geometry of the points I noticed that the new points were tagged with the CRS but not the original one. My bad for not including this before. I should have included the column headings. I have now managed to transform everything to 4326 and data displays correctly so Andre gets bonus. Jan 29, 2014 at 19:14
  • i would not mark this an correct answer , because everyone else seems to get it work correctly. or there is bug in @RussellFulton postgis Jan 30, 2014 at 7:57
  • 1
    I think the bug is in the workflow from CSV data into postgis. CSV is the only datasource without native CRS information, and a plugin using external commands on files can not access the layer CRS information assigned in QGIS.
    – AndreJ
    Jan 30, 2014 at 8:38

Your Answer

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

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