Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

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

Routing only with specified points

I'm very new to GIS and to pgRouting. We're implementing a Routing system in our app and we need to route between points that we'll define ourself. So far, we have a working pgRouting on Postgis (on ...
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
48 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
303 views

Using PostgreSQL as database with ArcGIS Pro? [closed]

Using ArcPro 2.5 I can connect to our PostgreSQL/PostGIS database (9.6.15/2.3), but I cannot edit the features. In ArcPro I get the error message "The enterprise database table is not registered ...
0
votes
1answer
112 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
40 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
17 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
265 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
35 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
29 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
35 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
358 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
42 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
25 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
20 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
34 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
40 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
32 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
43 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
13 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
48 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 ...
1
vote
2answers
43 views

how to convert the geometry of the elements of my table to move it from one srid system to another?

I have a table containing a column geom loaded from a shape file which was defined in lambert II system (27582), but when I loaded it I specified that I want to work in lambert 93 (2154). ...
0
votes
0answers
23 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
273 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
84 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
16 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
40 views

Export PostGIS data from one database to another with multiple geometry fields (ogr2ogr)

I am trying to copy the data from one database to another database. Both the source and target tables have 2 geometry columns -- a polygon field and a point field. There are NOT NULL constraints on ...
2
votes
1answer
35 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
55 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
28 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
42 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
65 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
80 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
38 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
56 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
546 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
236 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.
0
votes
1answer
32 views

Linestring table (of MultiLinestrings) ST_Intersection

I'm moving from GeoPandas to PostGIS and GeoAlchemy. I've a table full of multilinestring geometries (timezones from NaturalEarth.com) and I've a linestring which represents what will be the route of ...
3
votes
2answers
179 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
107 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
96 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
134 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 ...

1
2 3 4 5
67