Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

Filter by
Sorted by
Tagged with
1
vote
1answer
29 views

ArcGIS client user connection hanging when connecting to PostgreSQL geodatabase

ArcGIS Catalog and Pro are both hanging when I try to connect as a user that is a member of a role. I think it is related to the SDE schema permissions but I'm not sure. I have an enterprise ...
1
vote
2answers
51 views

How to manage different SRID data in same database table? [closed]

My data is in two different cities and three different EPSG code area. (Database is PostgreSQL/PostGIS.) So I think, I can not set SRID values by data. Making 3 different table is difficult. How can ...
0
votes
1answer
115 views

QGIS 3.10.2 - Install PostgreSQL and PostGIS

QGIS 3.10.2 in Ubuntu 18.04. I'm having problems installing PostgreSQL and PostGIS following the instructions here: Installing PostGIS under Ubuntu. This doesn't work, 'postgresql-9.1-postgis' isn't ...
1
vote
1answer
92 views

Removing same name layers from layer table in geotool?

I am making a geotool application in which I read the data from postgresql database. In this application if i make a buffer on a selected polygon then a buffer is created and buffer layer shown in ...
1
vote
0answers
43 views

How to store metadata in PostGIS [closed]

How can I include metadata for my data in PostGIS? I want to have the associated metadata for my shapefiles saved alongside the data in my database so that I can connect the database to QGIS and ...
0
votes
0answers
18 views

Created GeoTIFF only has NaN values in PostgresSQL

I created a GeoTIFF file from a numpy array, loaded it into a PostgreSQL DB and now I want to retrieve this raster data and convert it back into a numpy array. I know this seems nothing really ...
3
votes
3answers
281 views

Error transforming geometry into lat-long coordinates

I'm trying to transform some geometries from the postgis database into lat long coordinates: This is the query: SELECT st_x (geometry) as lng, st_y (geometry) as lat FROM final and this is the error ...
1
vote
1answer
37 views

What is the geometry type if I am having mixed geometry?

I have a mixed geometry data some belong to MULTIPOLYGON and others belong to MULTIPOLYGONZM and I want to insert the data into one.
0
votes
1answer
43 views

GeoServer does not display/preview pgRouting Layer

I am developing a routing application using GeoServer, PostgreSQL, HTML and JavaScript. To display the routing layer on the HTML webpage, I followed the instructions from this workshop. GeoServer does ...
0
votes
0answers
34 views

Connecting to Cloud SQL database in Colab runtime

I am very new to GCS and Cloud SQL, so forgive me if I am not asking this question well. A colleague and I have been working on a machine learning workflow that generates polygons from some ...
1
vote
1answer
36 views

Insert an input variable in a query in PostgreSQL

I'd like to insert an input variable (integer) in a query SQL (@variable) using the algorithm postgresql execute sql in the graphical modeler of QGIS. For example, in this kind of query INSERT INTO ...
1
vote
1answer
366 views

PostGIS to dissolve all features

I have a multi polygon with spatial field layer with and 3000 records and I want to use dissolve all in 1 record. On DISSOLVE algorithm in QGIS you have a choose to dissolve all features from layer ...
2
votes
1answer
45 views

Features of PostGIS layer are only selectable in attribute table but non on map canvas

My features of a PostGIS layer are displayed in the map canvas in QGIS and projected properly at the right place. They are not selectable in graphically in the map. But they are selectable in ...
2
votes
1answer
48 views

Inserting Polygon data into PostGIS using Python

I am able to insert polygon data manually into a postGres DB with one column having polygon datatype however, when I try to do the same dynamically it is not working. This works. cur.execute("...
2
votes
0answers
28 views

How to save each band in a new row with it's name, in PostGIS?

I have used PostGIS's raster2pgsql to import my multi-band raster data into my PostGIS database. Here is the code that can do this operation: raster2pgsql -I -C -e -Y -F /home/jj_user/input.tif ...
1
vote
1answer
22 views

GeoServer SQL VIEW

I am trying to add layer through SQL VIEW GeoServer. Everything is fine just, GeoServer is not able to take some chars correctly. When name parameter includes dash "-" character, the WMS not ...
3
votes
1answer
37 views

Get dimension of rectangular polygon PostGIS

Is there a way to calculate the width and height of a rectangular polygon such as the following? POLYGON((530934.26 161504.25, 530941.99 161515.92, 530945.36 161513.76, ...
1
vote
0answers
44 views

Efficient Way of Getting Intersections Between Segments and Shapes in Postgis

I have a set of 7849 tridimensional segments and 71 tridimensional shapes. My goal is to count the number of intersections between the segments and the shapes using Postgis. The segments were modeled ...
2
votes
1answer
35 views

Why PostGIS st_transform length is different?

I have two query select sum(st_length(shape)) from sde.ways; result is: 24268 And select sum(st_length(st_transform(shape, 3857))) from sde.ways; result is: 32047 Same Linestring geometries are ...
3
votes
1answer
47 views

Change datasource of multiple layers from one db to other db in QGIS project?

I have a QGIS project with around 100 layers referring to a postgres database. Since we're migrating to a new server, the database will be transfered too, but will be given a new IP. The connection ...
4
votes
1answer
3k views

How to use ST_Union with polygon and multipolygon geometry types?

I am attempting to combine the geometries for multiple records using the following: INSERT INTO schema.boundary (name, geom) SELECT 'Name' , ST_Union(geom) FROM schema.boundary WHERE id = 0; This ...
1
vote
1answer
32 views

Edit array type attributes in attribute table

I loaded a PostgreSQL layer in QGIS project. The layer has some array type attributes like integer[]. I'd like to edit these attributes in Attribute Table. But it doesn't work, I could not type any ...
1
vote
1answer
15 views

I cannot insert into a PostGIS raster table when I add raster constraints. “raster_table” violates check constraint “enforce_nodata_values_rast”

I have a raster table in PostGIS and I have a trigger that inserts into that raster table.I realized that for 130 different raster inputs, all of them voilated raster constraints. In other words, ...
1
vote
3answers
49 views

I would like to insert lines into a table that I have in my PostgreSQL db from various tables in my computer

I have a table on my PostgreSQL database that I would like to update by inserting the missing lines that I have in many other shapefile tables. I was able to connect to my server PostgreSQL from the ...
0
votes
0answers
28 views

JDBC authentication provider

In the case of JDBC authentication provider, after creating a connection with the database (e.g. user: postgres, pass: postgres) and defining the user in the GeoServer (e.g. user: test, pass: test), ...
2
votes
1answer
275 views

Assigning attributes values to PointZM and convert to LineZM Geometries in QGIS? [closed]

I have elevation and measured values in attribute table of points layer. Is there a way to convert this layer into PointZM (with elevation and measured) and assign elevation and measured values to ...
4
votes
2answers
85 views

Select points falling outside of buffer and count

I am currently learning PostGIS and following the course offered by Datapolitan. I want to do the steps mentioned in slide 200 https://training.datapolitan.com/qgis-training/Intermediate_GIS/#200 ...
12
votes
2answers
13k views

Import large CSV file into PostGIS

I am trying to import CSV files into PostGIS. Following this post, I have created tables before. I found other suggestions saying that I can run the copy command. If I run this command: COPY table ...
0
votes
0answers
17 views

Adding rasters to pgsql with rpostgis - why are raster constraints so slow?

I am adding rasters to a postgresql DB using the function pgWriteRast from the package rpostgis. I am noticing that the additions to the DB are slowing down as more and more rasters are added to the ...
2
votes
1answer
44 views

Double precision error on user defined type

There is user defined type which consist of double precision type and integer. It looks like it CREATE TYPE ZOOM_LVL AS ( eps FLOAT8, minptrs INTEGER ); This function is used to set the value to user ...
0
votes
2answers
59 views

Calculate median width of road polygons

I'd like to calculate the median width of a polygon in PostGIS. The problem is almost identical to the one described here, which has an excellent answer. My question is how to calculate the median, ...
1
vote
0answers
30 views

linux gdal 3.1.0 ogr2ogr ERROR 1: Unable to find driver PostgreSQL

I have seen a number of posts similar to this but I have another issue. I try to complie gdal-3.1.0 to import osm.pbf to PostgreSQL. My os is Red Hat Enterprise Linux Server release 7.6. I need Oracle ...
0
votes
1answer
44 views

PostgreSQL/PostGIS - Insert values in a new column with a case statement [closed]

I'm writing a SQL script in PostgreSQL and I need to reclassify certain values to a new column that form a label of the values. For example, the values of column A range from 1 to 10 and I want to add ...
1
vote
1answer
76 views

QField and Inputapp with PostgreSQL available on the internet

I have been using both tools in field work, making use of forms to facilitate filling in information using spatial databases in geopackage format. It has worked very well, but we have to take care to ...
4
votes
2answers
82 views

osm2pgrouting creates erroneous roads

osm2pgrouting works without giving any errors but most of the roads have lost their geometry. Result is ; What I've done so far; OSM Data downloaded from http://download.geofabrik.de/ in pbf ...
1
vote
1answer
41 views

Clip raster with postgis outputting result in clipping geometry extent

I want postgis st_clip to output clipped result in extent of initial clipping geometry. Currently, it's crop option can be set to output either in initial raster extent or in extent of intersection ...
-1
votes
1answer
70 views

<ServerException>Layer(s) not valid</ServerException>

I am using QGIS Desktop 3.12.3 and QGIS Server and I have the following issue: a WMS generated based on a set of data stored in a Postgres db returns on GetCapabilities: <ServerException>Layer(s)...
2
votes
1answer
47 views

Trying ST_Slope with SRTM rasters and a shapefile but the output raster has different SRID

I have two SRTM rasters https://dds.cr.usgs.gov/srtm/version2_1/SRTM1/Region_06/ (N40W074 and N40W075) with Datum "WGS_1984" I've united them to be able to work with one raster: CREATE TABLE rasters....
2
votes
1answer
18 views

Complexify geography polygons in PostGIS

Is it possible to modify polygons (essentially, make them more complex by inserting additional points) so that their naïve point-linking rendering would be a closer match to their respective geodesic ...
0
votes
1answer
554 views

Edit a view of a PostGIS table in QGIS

I have a simple layer in PostGIS created as follows: CREATE TABLE layer ( id serial PRIMARY KEY, geom geometry(LINESTRING, 31370), ... ) To avoid duplicate information, I created a view ...
3
votes
1answer
238 views

Calculating polygon main direction and creating line out of it

I would like to find out the orientation of a polygon and create a line of that direction.See image below.
3
votes
2answers
181 views

PostGIS difference and union

I have two tables, A and B. I want to Union(/Dissolve) B and then difference A,B. To get whatever is left of A when overlapping B is erased: select a.someid, st_difference(a.wkb_geometry,st_union(b....
2
votes
1answer
117 views

Find closest polygon from point and get its attributes?

Given a set of points and a set of polygons, I am trying to find for each point what the closest polygon is and bring over the attributes from that polygon to the point (together with the actual ...
1
vote
1answer
103 views

PostGIS create table with multiple geometry

I want to create a table with a geometry field in it. e.g.: CREATE TABLE ptgeogwgs(gid serial PRIMARY KEY, geog geography(POINT) ); In my case, this geometry field can either be a POINT, a ...
1
vote
3answers
137 views

Reducing the processing time when handling millions of 3D points to 3D lines in PostGIS

I am trying to calculate the minimum distance between 16 millions of 3D points and 3D lines. The query is working for more or less 1 million data, but after that, it took so long and never ends. All ...
2
votes
1answer
1k views

Snap point to point with St_Snap Postgis

I write a request that allows to snap a point to another. Here is the request: SELECT      f.gid as gid,     ST_Snap (f.Geom, g.Geom, min, 5) as geom FROM      boiten as f,      (...
3
votes
1answer
503 views

Use NTv2 grid files in PostGIS ST_Transform

This question is related to How to use gridfiles (*.gsb) in QGIS Situation is the following: The nationwide standardized gridfile for the transformation of geotopography data BeTA2007 is long since ...
5
votes
1answer
334 views

PostGIS - unique constraint for spatial equality

I have a polygon column for which I would like to add a unique constraint that tests for spatial equality akin to ST_Equals so that it ignores vertex order. For instance, I would like these polygons ...
2
votes
1answer
220 views

Trigger PostGIS QGIS

I have 2 layers on PostGIS: a point layer named 'tdia' and a polygon layer named 'com': -the com layer contains the city name. (Polygon) -the tdia layer contains bus stops. (point) I want to create a ...
4
votes
1answer
236 views

Using ST_Segmentize to PostgreSQL table of segments?

I have a PostgreSQL table with straight lines (segments) that I need to subdivide in further segments so that none of them is more than 50 metres long. I am new using PostGIS (and SQL) and I'm ...

1
2
3 4 5
67