New answers tagged postgresql
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
...
- 69k
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' )
- 382
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 ...
- 19
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 ...
- 7,364
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