New answers tagged

4 votes

Is there a way to access named PostgreSQL connections via PyQGIS in order to access and load spatial layers?

Ah I think I actually figured it out from this page: Getting schema names from PostGIS database in PyQGIS For connections that exist, you can use: md = QgsProviderRegistry.instance().providerMetadata('...
  • 69
6 votes

Is there a way to access named PostgreSQL connections via PyQGIS in order to access and load spatial layers?

Try this. I have a connection called localhost data so I search for the word data in all the connections: from PyQt5.QtCore import QSettings qs = QSettings() L = [] for k in sorted(qs.allKeys()): #For ...
  • 60.7k
5 votes

I need to expect multi value with if statement

You need to replace ' and' with ''. E.g. Ahmed and Marten -> Ahmed Marten, and then check replaced_name is title(replaced_name). Use this expression: with_variable( 'text', -- variable name ...
0 votes

I need to expect multi value with if statement

I don't know if I understand your question correctly. But try something like: if( strpos( 'the name you are searching for' , "name" ) > 0 , 'yes' , 'no' )
2 votes

Unmatched records missing from spatial left join

The issue come from : where pnt.fieldx = 'xx' If no point are matching, pnt.fieldx is NULL, so the WHERE is not true. Just replace the where clause: where pnt.fieldx = 'xx' or pnt.fieldx is NULL Or ...
  • 458
8 votes
Accepted

Unmatched records missing from spatial left join

Try moving the where clause to a join condition: select plg.id,p lg.geom, count(pnt.id) as num_pnt from polygons plg left join points pnt on st_contains(plg.geom,st_transform(pnt.geom,3003)) and pnt....
  • 60.7k
1 vote
Accepted

Locate point along line in multiple self-intersection area

Also ST_Split return just one element in that case, I solved like this I post here if someone need help just ask.. assume that exists a table of nodes and a table of lines. this is a trigger function ...
2 votes

Using a intersects spatial query as default value in PostGIS table

If you are on PostgreSQL > 12.0 you could elegantly use Generated Columns: create a function to retrieve <field_1> values (as array) from <table_a> found for all <geom> that ...
  • 26.9k
2 votes

GeoJSON from SQL View

You seem to have a logical misconception in your approach, leading to the syntax issue: you are sending a FeatureCollection to the server to eventually get passed into the query - however, ...
  • 26.9k
9 votes

Documentation for <->

You'll find an exhaustive overview of supported operators in the PostGIS Function reference under Operators. The more or less canonically named spatial (K)NN operator <-> is documented under ...
  • 26.9k
2 votes

Convert SQLite3 Geometry BLOB to WKB for PostgreSQL

Bytes 0-38 are all specific to SpatiaLite BLOB and WKB begins after them. You can find Java code from https://github.com/openjump-gis/openjump/blob/main/src/com/vividsolutions/jump/datastore/...
  • 58.9k
5 votes
Accepted

Calculate distance between two consecutive points within the same group in PostgreSQL

You can use partition by in your window: SELECT id, participant, ST_Distance(geom, lag(geom, 1) OVER (PARTITION BY participant ORDER by id ASC)) FROM table;
  • 2,317
0 votes

GeoJSON from SQL View

NB: This answer was for an earlier edition of the question which was using GET rather than POST. Therefore this answer is no longer relevant, due to edits to the question. OPTION 1 Use an HTTP POST ...
3 votes

Ogr2Ogr appending duplicate records (2X Times) to PostgreSQL Table

Switch -append is adding new features into the table. If you already had a table with 3620 rows then the result is expected. If the table was empty then the result is strange. If you want to replace ...
  • 58.9k
0 votes

Importing GeoJson data to postgresql using ogr2ogr command

Your data are valid JSON but not valid GeoJSON. You can test yourself by pasting this into https://geojsonlint.com { "type": "FeatureCollection", "properties": { "...
  • 58.9k
0 votes

PostGIS: type "raster" does not exist

For me the answer was: run create extension postgis_raster from a SQL client. I had never used this extension, only vanilla postgis, but I was trying to install a PostGIS "add-on" that ...
  • 1,215
1 vote
Accepted

Error inserting data into PostGIS/PostgreSQL database through postgres_fdw Foreign Data Wrapper

The search path of the user used for the remote connection is restricted to the current schema. The trigger or view-function must therefore add public to its path (or whichever schema contains postgis)...
  • 37.8k
0 votes

How do I solve "django.db.utils.OperationalError: near "CASCADE": syntax error" in GeoNode install?

NOTE: Spatialize is no longer supported on Geonode, please use a Postgres db with PostGIS instead To setup-up, a PostgreSQL database is enough to change the GEODATABASE_URL and DATABASE_URL. Plus ...
  • 101
1 vote

Create half ellipsoid on Postgis

This error message basically tells you that the geometry column in the database is not a 3D geometry (it doesn't have the Z value), hence you have to create, e.g. a POINTZ instead of a POINT. Same ...
  • 9,081
0 votes

Change TileServer in pgAdmin used by geometry viewer

If you are using the pre-packaged version (rather than building it from source after making changes), you may need to make changes to the generated file under web/pgadmin/static/js/generated/sqleditor....
  • 481

Top 50 recent answers are included