Why are some points in my PostGIS layer not displayed?

Question

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

Answer 1

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)

Answer 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...

Answer 3

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.