Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

Filter by
Sorted by
Tagged with
1
vote
0answers
53 views

Optimizing an intersection between a single massive multipolygon (WKT) and many features from PostGIS

Context I have a massive MultiPolygon (~90'000 vertices, super complex, spreading over 60km², with lots of holes and parts) resulting of some Python computation. This MultiPolygon is coming as a WKT ...
0
votes
0answers
8 views

Move deleted Parcels features to archive featureclass - Enterprise geodatabase

I have an ArcGIS Enterprise Geodatabase in PostgreSQL with a parcel feature class. The parcel feature class is versioned and enabled for multiuser editing. Users accidentally deleted some parcels from ...
3
votes
1answer
33 views

QGIS Model Designer - populate input options with values from postgresql database

EDIT: added further explanation with pics Is it possible to populate an input field (enum etc.), with options that are obtained from postgresql database? Similar to how you can already select database ...
1
vote
1answer
49 views

PostGIS ST_Intersects replace NULL value with 0

I'd like to count the number of points that interact with a given polygon using PostGIS. Below is my query SELECT DISTINCT COUNT(pt.geom) OVER w sum_of_pt FROM pt, poly WHERE ST_Intersects(pt.geom, ...
1
vote
0answers
26 views

How to import PostGIS into BigQuery GIS?

I have to import PostGIS data into BigQuery GIS. In order to do this, I dumped the PostGIS data in CSV and import it into BigQuery. This is working for all the columns but not the geometric one. If I ...
3
votes
1answer
32 views

View two geometry columns together in pgAdmin Geometry Viewer

I have two geometry columns like this: How can I see them together in the Geometry Viewer? If i select all rows it shows only one column at a time.
1
vote
0answers
28 views

Create function for large dataset (without specifying co-ordinates)

I have created a function within postgres/pgrouting to route from place x1/y1 to x2/y2. I have been able to manually route between all x1/y1 (from_) to x2/y2 (to_) using the ARRAY function ARRAY(...
0
votes
1answer
26 views

Very Slow OSM Tile Rendering

I have built my own OSM server of North America using switch2osm's Ubuntu 20.04 guide and all went well. Except when it comes to rendering tiles...or pre-rendering. Here is the most recent completed ...
1
vote
1answer
16 views

Change TileServer in pgAdmin used by geometry viewer

Having exhausted other search options, I was hoping someone might know of a way to change the pgAdmin geometry viewer default tile server? It tries to bring up the OSM one, but we are behind a ...
1
vote
0answers
28 views

Missing some data from query in PostgreSQL

I am using this query to get elevation data from PostgreSQL: WITH points(geom) AS ( values (ST_SetSRID(ST_Point(980279.44, 6431129.89), 2154)), (ST_SetSRID(ST_Point(982573.24, 6429308....
1
vote
0answers
16 views

Building, vertical alignment and urban densification [closed]

I am looking for a methodology to select using a land layer (plot) and a building layer the land where the buildings have low height and are located between lots with high buildings. Has anyone ever ...
1
vote
0answers
42 views

Export query from DB Manager QGIS

I have following query: SELECT NOPARC, MAX(LEN),geom_from_wkt(geometry) AS geomd FROM lines group by oznakas And I get following output: My goal is to export this to external shp file with ...
2
votes
0answers
20 views

MapServer: How to define connection string outside of mapfile?

Is there a possibility in MapServer to store the connection string in an external file and reference it in the mapfile? I have basic Postgres connection strings like this: CONNECTION "user=...
5
votes
1answer
59 views

Applying ST_Intersects in a query with PostGIS

I have a PostgreSQL/PostGIS materialized view (study_region_all) with stream lines and a table of county boundary polygons (counties). They both have a column called co_fips but for a subset of items ...
2
votes
2answers
37 views

How to draw lines from point-to-point, In PostGIS / PostgreSQL

I have some GPS points in PostGIS, and I’m looking for a way to draw lines from point to point, to illustrate travel routes. I know how to do one long line, but I need to do small point-to-point lines....
5
votes
1answer
84 views

ST_Transform error: latitude or longitude exceeded limits(-14)

I am trying to run a query in PostGres on a multiline spatial feature class table. Among other things, I am trying to use PostGIS to get the shape length of the reprojected geometry. My query looks ...
7
votes
1answer
157 views

Performing merge in PostGIS

By means of the PostgreSQL/PostGIS I want to replicate a function from a Python script into a SQL query. In my Python script, I have a list of feature classes and I use ArcPy's Merge_Management to ...
2
votes
1answer
54 views

PostgreSQL query from large raster table problem

I am trying to build a fast query to PostgreSQL. My raster (DEM) is 1.6 GB, about 2.5 cm precision...With a smaller DEM it was ok, about 20 seconds to query a 300m radius elevations. Now with this ...
2
votes
0answers
23 views

Data warehouse for ERA-5 data

I'm new to GIS an this kind of data. I want to create for my team an easy way to obtain weather data. We are using https://cds.climate.copernicus.eu/cdsapp#!/dataset/reanalysis-era5-land?tab=overview ...
1
vote
1answer
35 views

Make line geom from two point columns which are the result of a join

I have the following query which joins two tables and returns two geom columns, I would now like to make a line between the two geom columns and set that as the Geom for the Drops table. Table 1 - ...
0
votes
0answers
40 views

Transferring/inheriting GIS database structure in PostGIS from old project to new one

I have been given an existing GIS database design for a solar power project, with its structure, relationships, geometry etc designed from another project and existing in PostGIS database. I am ...
2
votes
1answer
53 views

Error reprojecting a layer

I have a layer on ESPG 4326 and I want to reproject it to ESPG 3857. I'm using this SQL query: ALTER TABLE table ALTER COLUMN geom TYPE geometry(Geometry, 3857) USING ST_Transform(ST_SetSRID(...
4
votes
1answer
42 views

ST_DWithin is returning all the data in the table

I am new to PostGIS and currently struggling with ST_DWithin function. I have a table stores, which stores lat and long of locations in Denmark in the location column as geometry and with SRID 4326. ...
0
votes
0answers
17 views

Automatic creation of image mosaic every day with gsconfig

I want to create an image mosaic every day with the Python module "gsconfig". I managed to create the first image mosaic (I can open the WMS flux and play with the dimension time). But, when ...
4
votes
4answers
99 views

Saving modifications in PostGIS layer edited in QGIS

I opened a PostGIS layer (from the server) with QGIS and I did some modifications (for example attribute join from another layer). How can I save my modifications so that it is visible in pgAdmin?
3
votes
0answers
70 views

Error using PostGIS query in MultipolygonZ type data [duplicate]

I am using this query in MultipolygonZ type data in my PostgreSQL database, but I got error for so many times the data is not rendered properly in PostgreSQL with PostGIS extension. PostGIS query:- ...
3
votes
1answer
26 views

Intersecting polygons with raster in PostgreSQL using PostGIS and pg_raster

My goal is to use elevation data I loaded into PostgreSQL using pg_raster and intersecting it with a polygon. Within this polygon I would like to find the largest value in the pg_raster. My first ...
1
vote
0answers
12 views

Table join only inserting values depending on values in column from other table [closed]

I have two tables. One table with areas of certain types a.matrikulaere_foreninger with 274 rows and another table with a column called b.pnr. I want to create a view where the pnr from table b is ...
5
votes
1answer
76 views

Segment lines with incremental attributes - PostGIS

I have been trying to segment lines at specific interval (2000 m) using St_LineSubstring function. So far, I managed to accomplish that with the following code: SELECT ST_LineSubstring(the_geom, 2000....
3
votes
1answer
21 views

Setup timeout parametr in QGIS PostgreSQL connection

Is it possible to setup timeout parameter for PostgreSQL in QGIS PostgreSQL / PostGIS connection? I want to change default timeout value (infinity timeout) for DB connection in QGIS. In GUI I did not ...
3
votes
1answer
36 views

ST_3DIntersection with LINESTRING M and POLYGON M

I have a database of aircraft positions and I'm trying to develop a way to recreate what airspace looked like at a time T. So my idea was to store each pair of positions as a LINESTRING M(lon1 lat1 t1,...
3
votes
1answer
39 views

Union multiple LineStrings and convert MultiLineString to Linestring

I have three LineStrings which I want to ST_Union() by a column id to become one LineString. SELECT id, ST_Union(geom) geom FROM lines GROUP BY id The resulting geom however, is of type ...
4
votes
1answer
74 views

Compound contiguous links to lines in a network and find isolated lines

I want to compound the red and green links (lines between the poinst) to one or more lines. I need it to finde the green links that not touching the red lines in my network. Step 1: compound touching ...
2
votes
0answers
35 views

How to make a column in a joined layer editable?

I have layer1 from postgresql which has is joined to layer2. Most users work on layer1 but there is a column on layer2 which needs to be updated by users of layer1 Without making layer2 fully ...
3
votes
1answer
51 views

Finding isolated links in a network of links with PostgreSQL

I have a street network from many of links in a PostgreSQL table. Some links have no connection to the network. I want to isolate these links from the network. Each link has a geometry (geom) and id ...
4
votes
1answer
75 views

PostGIS clustering with statistics

I'm discovering PostGIS for myself and right now I'm stuck at the point with clusters. Found some tips about how to make clusters on point layer in PostGIS by using ST_ClusterWithin function. Right ...
2
votes
2answers
39 views

Get the subline of a LineString consisting of all intersecting POINTS without knowing start and ending node in PostGIS?

I have a a set of LineStrings in PostGIS just like this one (could be of any, not-self-intersecting shape): I then get intersecting POINTs on that line like this: What I would like to get now is the ...
11
votes
4answers
170 views

PostGIS ST_ConvexHull() but perpendicular to a line?

I have a rather special requirement for a PostGIS geometry manipulation. Consider the following case: I have a polygon and a line feature. If I do a ST_ConvexHull() or ST_ConcaveHull() I would get ...
3
votes
2answers
45 views

Get max value from points based on distinct id, and keep geometry for the new selection

I have a pointfile of approx.. 226000 points, where I need to filter by max value and distinct id_pol. SELECT distinct (id_pol), MAX(value) FROM fillspill_pol_value GROUP BY id_pol; This ...
3
votes
1answer
74 views

ogr2ogr append script keeps incrementing id field in PostgreSQL/PostGIS table

I have a geopackage (from the 'input' app) which is being inserted into a postgres/postGIS table via an ogr2ogr script on a daily basis: ogr2ogr -append -f "PostgreSQL" "PG:user=xxxx ...
2
votes
1answer
50 views

PostGIS trigger : table and materialized view

I have a PostGIS polygons table for my "cities". I have a point table for my "boreholl". I created a "mv" materialized view defined by a spatial join as follows: SELECT ...
2
votes
0answers
41 views

Clustering spatial data in database

I have a simple website that is hosted on 000webhost. This service provides storing data in MySql database for a free plan. I wanted to extend some functionality for my site by clustering loads of ...
0
votes
1answer
27 views

Generating edge table from nodes using QGIS

I generated nodes from a line road network in QGIS and uploaded them to PostgreSQL/PostGIS. I would really like to use them for network analysis and I am wondering if anyone knows if, from these ...
2
votes
0answers
35 views

Union based on string of identifiers PostGIS

I have a table of documents relating to several parcels of land. I have in this table the identifiers (numerals, sometimes interspaced by "/"- text field, delimited by commas). I also have a ...
1
vote
0answers
36 views

Execute Function After Saving PostgreSQL Layer - QGIS

I have a QGIS project with many PostgreSQL layers. One of these layers is the result of processing some other layers. So each time any of the other layers are modified in any way that layer should be ...
0
votes
1answer
42 views

Implementing PostgreSQL table trigger with GeoServer JDBC user service in Leaflet app

I've been using a table trigger on a PostGIS table to track changes for some time now -- it saves username, date, time, etc in a history table (based on this PostGIS doc). Now, I'm developing a ...
0
votes
0answers
43 views

Installing Lizmap with PostgreSQL and Azure

In our service we have already installed Lizmap on an Azure server, so it works fine but it's just with shp, GeoJSON files. I suggested to them that we work with a database that I created on a ...
6
votes
3answers
337 views

Invert the ST_DWithin() function

Is it possible to perform a query like this: SELECT * FROM foo JOIN bar ON ST_Dwithin(foo.geom, bar.geom, > 10); -- the > 10 means that I want to select everything that is ...
2
votes
1answer
59 views

PostGIS - st_intersects: WHERE vs INNER JOIN

In PostgreSQL/PostGIS, when running an intersection between 2 geometries, what the difference between these two syntaxes ? -- Method 1 SELECT a.id b.id FROM a, b WHERE st_intersects(a.geom, b.geom) --...
3
votes
2answers
65 views

ST_Disjoint() gives unexpected result using PostGIS

I am fiddling around with this dataset http://s3.cleverelephant.ca/postgis-workshop-2020.zip. It is used in this workshop http://postgis.net/workshops/postgis-intro/spatial_relationships.html. I want ...

1
2 3 4 5
75