Bug report #20487

Geometry type not detected when loading query to canvas using MakePoint for geometry (DBManager)

Added by Chris York about 6 years ago. Updated almost 6 years ago.

Status:Open
Priority:Normal
Assignee:-
Category:DB Manager
Affected QGIS version:3.4.0 Regression?:No
Operating System:Windows 10 Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:28307

Description

I have a GeoPackage with a table containing coordinates as separate columns (imported from an external application, originally CSV) - there are multiple sets of coordinates in each record and I am loading a series of layers from each record using a different subset of the fields within the record.

To define the geometry of the layer my query includes:

MakePoint( "A-Longitude" , "A-Latitude") as geometry

When the layer is loaded to the canvas the layer styling is set to a Line so nothing is displayed. By changing the Layer styling to "No Symbols" and then back to "Single symbol", the geometry type is detected and the points appear on the canvas. However, labels can be displayed at the correct location before the layer symbology is changed.

I am guessing that the either canvas is not detecting geometry type on load or DBManager is not providing that, and the default type is a line?

Issue also occurred in 2.18, 3.0 and 3.2

test1.gpkg (96 KB) Giovanni Manghi, 2018-12-11 02:17 PM

History

#1 Updated by Giovanni Manghi about 6 years ago

  • Status changed from Open to Feedback
  • Category changed from Map Canvas to DB Manager

Please attach a sample of your data.

#2 Updated by Chris York about 6 years ago

Giovanni Manghi wrote:

Please attach a sample of your data.

A sample point from CSV (with the location 'rounded'!) - I loaded the CSV as a delimited text file as a layer and imported the layer into a GPKG database as a non-spatial table (MyTable). I have also imported directly into DBManager using a csvt file to ensure field types are correct

Number,Reason,Start-Latitude,Start-Longitude,Start-Altitude,Start-Accuracy,Photo1,P1_location-Latitude,P1_location-Longitude,P1_location-Altitude,P1_location-Accuracy
1,Start,56,-4,10,3,6798.jpg,56,-4,10,4

Table structure (copied from DB Manager)

\# Name Type Null
0 fid INTEGER N
1 Number MEDIUMINT Y
2 Reason TEXT Y
3 Start-Latitude REAL Y
4 Start-Longitude REAL Y
5 Start-Altitude MEDIUMINT Y
6 Start-Accuracy MEDIUMINT Y
7 Photo1 TEXT Y
8 P1_location-Latitude REAL Y
9 P1_location-Longitude REAL Y
10 P1_location-Altitude MEDIUMINT Y
11 P1_location-Accuracy MEDIUMINT Y

My query is:

Select "Number", "Reason", "Photo1" as Image,"P1_location-Accuracy" as GPS_Accuracy, MakePoint( "P1_location-Longitude" , "P1_location-Latitude") as geometry from MyTable

In DBManager I select 'load as new layer' and use "Geometry" as my Geometry column

This is a simplified version of my data - the actual table has many more columns... I also have a separate unique key which I add to the query, which is another column from MyTable.

#3 Updated by Giovanni Manghi about 6 years ago

  • Status changed from Feedback to Open

SQL console in db manager gives empty error messages dialogs, so I give up try troubleshooting this.
For real work stay on 2.18 still.

#4 Updated by Alessandro Pasotti almost 6 years ago

If somebody could share a test project and data I could have a look.

#5 Updated by Giovanni Manghi almost 6 years ago

Alessandro Pasotti wrote:

If somebody could share a test project and data I could have a look.

attached

Also available in: Atom PDF