Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

0
votes
2answers
57 views

Using geometry column as primary key

I have a PostgreSQL/PostGIS table which stores data about all the warning signs around the world. The map data is provided to me by our vendor, and the data is such that no possible combination of ...
1
vote
1answer
48 views

How to import a shapefile from database into QGIS plugin?

I am building a QGIS plugin, and want to add some base maps from the shapefiles which were stored in the PostgreSQL database. My question is: Is there a way to import shapefiles stored in the ...
1
vote
0answers
56 views

Why coordinates are saved like “0101000020110F00003CDBA337DCC351C06D37C1374D374840” in db? [closed]

On my DB, the data looks like this: In the DAO I am executing a query: public Object login(UserEntity user) { StringBuffer sb = new StringBuffer(); sb.append("SELECT user FROM ...
0
votes
1answer
66 views

Where is the error in this PostGIS spatial Join Query? [closed]

I am trying to write a query (or more actually a query template) to do a PostGIS Spatial Join between two shapefiles and therefore. Mainly this is between a Point Shapefile and a Polygon Shapefile. ...
0
votes
0answers
26 views

Optimal way to find points within polygon in PostGIS

I've developed an query to find points which are completely falls inside polygon by using PostgreSQL 10 and PostGIS 2.4 database. The query took long time (aprx 90mins) to produce the result. The ...
1
vote
1answer
43 views

How to perform isochrone in PostGIS by pgRouting?

In this link the person who answered, missed to say the explanation of the answer about what function used by him to get the output for isochrone. I want to do the same but I got the same result like ...
1
vote
0answers
28 views

PostGIS ST_Point_Inside_Circle radius unit [duplicate]

I'm trying to use the ST_Point_Inside_Circle function from PostGIS but I'm not sure about the unit the radius is stored. Let's say I have a table with the following points: Id | Point 1 | ...
0
votes
0answers
22 views

Split line layer by another line layer

I have two line layers . One is the stream network (dissolved) and other is the point layer converted to line layer by converting each point into short line segments. I am using "Split by lines" tool ...
3
votes
1answer
66 views

Converting point into line using QGIS

I have a set of points with datatype as geometry(MultiPoint, 32643), I want each point to be converted in short line segments. The purpose of doing this is to use "split by lines" tool in QGIS which ...
0
votes
1answer
36 views

shp2pgsql Load Data Type Inconsistent

I'm having trouble when trying to upload a shape file in postgresql/PostGIS. There's a field with type "date", but actually there'r two elements with content : '20012060' and it returns ERROR when I ...
0
votes
0answers
40 views

Merging Degree Two nodes in Stream Network using QGIS 3.4

I have a stream network and I want to convert it in graph like data structure, for which I need to have precise set of FROM and TO vertices.The geometry of stream network is not correct i.e. the ...
1
vote
0answers
32 views

Vector tile generation

I have a full planet imported in PostgreSQL with imposm3. What is the best way to generate vector tiles (MVT in .mbtiles) from PBF/PostgreSQL with maximum hardware utilization (distributed generation ...
1
vote
3answers
70 views

Extracting single boundary from multipolygon in PostGIS

I am trying to find a single external boundary either polygon or line from the multipolygon using st_boundary in PostGIS query. The function doesn't work. What could be the issue?
2
votes
1answer
36 views

PostgreSQL - Array of overlapping Polygon Ids

I have a database table (overlay) that has columns, id::int, intersections::int array, geom::polygon. I have another table (images) that has id and geom columns. I am trying to populate the ...
2
votes
0answers
38 views

Finding Intersection Points in Stream Network using QGIS?

I have a stream network and I want to find the red points as shown below. I have tried using Q-chainage and Line Intersection feature in QGIS but it gives more than expected number of points. The red ...
1
vote
1answer
79 views

psycopg2.ProgrammingError: function does not exist

I am using Python code cur.callproc('function_a',[parameter1, parameter2]) for executing one function and this code is correct when I use a user having full DB permissions. But when I use a private ...
1
vote
1answer
27 views

How do I make my raster layer transparent where there is no data?

I have just managed to load some TIF data into PostgreSQL 11 using raster2pgsql. This test contained just a few files. When I load the resulting layer into QGIS, the data displays perfectly, expect ...
2
votes
0answers
27 views

Using QGIS widget value-relation with PostgreSQL tables?

I am trying to implement types and subtypes to a layer of points that is stored in PostgreSQL and I wonder if it is possible to use Value Related Widget of QGIS, in PostgreSQL? I followed the ...
1
vote
0answers
33 views

Simple clustering example

I'm trying to use postgis ST_withincluster function. But my knowledge over SQL seems a bit too fresh at the moment and I can't figure out by myself how I should proceed by reading posts like this one: ...
0
votes
1answer
20 views

Using ogr2ogr for converting tables from non-public schema to GeoJSON

I've used ogr2ogr to convert tables in PostgreSQL database to GeoJSON which works fine for the public schema. The question is how can I set the schema to non-public to access my tables? I've already ...
1
vote
1answer
18 views

What is a compatible data type for 'specific' date and time? [closed]

I need to import CSV file into PostgreSQL, but I got an error on time column which has data like this: 11/1/2016 12:00:00 AM I tried to make column and assign 'timestamp' data type, but it's just ...
3
votes
2answers
45 views

PostGIS st_makeline extra columns in result

From a table of point data, there are some groups of points that can define a line (cross sections of a water course). 'cross_section' is the name of the table with the point data, geometrypoint the ...
1
vote
1answer
45 views

PostGIS: Running multiple intersections within a single SQL query?

PostgreSQL 11 + PostGIS, on Windows 10. I'm working with a PostGIS-enabled database. I want to UPDATE a large polygon table (100,000+ rows) WHERE the geometry of that host table intersects the ...
2
votes
0answers
34 views

Creating GeoJSON REST service

I'm trying to create a GeoJSON REST service from a PostgreSQL connection. First I fetch GeoJSON as a string from PostgreSQL: import java.util.*; import java.sql.Connection; import java.sql....
3
votes
2answers
92 views

Re-order multipart polygons in PostGIS

I have a table with multpart polygon geometry with coordinate system EPSG:4326. I can identify, what are geometries with more than 1 polygons in it. I want to re-order it in a way that the largest ...
1
vote
0answers
41 views

How to create a function for getting a largest polygon from 2 polygons using case statement in PostgreSQL?

CREATE OR REPLACE FUNCTION public.largest_polygon1( geom1 geometry, geom2 geometry) RETURNS geometry LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ begin Return Case ...
1
vote
1answer
38 views

How to calculate extreme point (most north or east etc) of geography(polygon) type in PostGIS

I have a table which include geography(polygon) type. CREATE TABLE place ( name varchar(100), geom GEOMETRY(POLYGON, 4326) ); insert into place SELECT 'place1', ST_GeomFromText('POLYGON((-71....
1
vote
1answer
52 views

PostgreSQL - Creating DB, User privileges [closed]

I created a database named "wchiba" in postgres using : create database wchiba; Then I create a user : create a user saifeddine with password "0000"; Then I grant privileges : grant all on ...
0
votes
1answer
22 views

password authentication failed for user “ASUS”

I'm creating table in PostgreSQL using these commands: shp2pgsql -s 32632 watershed.shp watershed > watershed.sql psql -f watershed.sql cambridge psql -f watershed.sql cambridge But I had ...
1
vote
0answers
22 views

Using CSW for GeoServer PostGIS database

I am quite new to GeoServer and general OGC fundamentals. I have GeoServer set up and I set up a PostGIS data store which links to a database I have already been able to make successive calls using ...
2
votes
1answer
33 views

raster2pgsql: queries on imported raster are NULL

I loaded a tif-file of some soil data into postgres with: raster2pgsql -c -I -F -C -r -M -s 31467 -t 200x200 soil_file.tif gis.soil_map | psql -U user -d database -h localhost -p 5432 which worked ...
1
vote
1answer
67 views

Type geometry does not exist PostGIS [duplicate]

I am trying to add a geometry column to an existing table in PostgreSQL inside a plpgsql function using EXECUTE format('ALTER table %I add column geom geometry(Point, 102003)', target_table); Its ...
0
votes
1answer
136 views

How to create a function for finding the largest of 2 polygons by area in PostGIS?

I tried my level in creating the function but it shows error while running. I have enclosed the code here : create function largest_poly (geom1, geom2) returns geometry as $largest_poly$ begin from ...
1
vote
1answer
69 views

Mapping land plots which contain two buildings

I need to show land plots in QGIS that have two buildings. My query only shows buildings which are placed on land plots. Create or Replace VIEW dvi_budivli_view AS Select "budivly".*, "dilyanki" ...
1
vote
2answers
56 views

shp2pgsql command to import shapefile [closed]

I have a problem loading my shapefile named watershed and link it to my table in postgresql named watershed as mentioned here: shp2pgsql -I -s 32632 C:\Users\ASUS\Desktop\master_PEER\DATABASE\...
1
vote
1answer
44 views

Renaming duplicates with any vacant value within a certain interval

The values of my column of interest (col1) should range from 0-999. I'm looking for a way to assign one of each duplicate a new value that lies within my this range 0-999. Only features that are ...
2
votes
1answer
60 views

Improving Mapnik render time

I'm adding polylines (elevation curves) to a world map. Said polylines are stored in a PostgreSQL database from gdal_contour tool. The geometry columns (LineString, 3857) has a GIST index and a ...
2
votes
0answers
23 views

Point cloud query is very slow and not using parrallel scan or more memory

The following PostGIS query is running very slow (> 1 day for about 1 million records): UPDATE test.table1 SET area= ST_Area(PC_EnvelopeGeometry(pa)) WHERE area is null or area <> ...
0
votes
0answers
20 views

How to update geometry in PostGIS for features that are in an ArcGIS enterprise geodatabase (SDE)?

UpdateGeometrySRID is not working for some reason, I think this may be due to it being an enterprise geodatabase? I also tried the manual version of UpdateGeometrySRID, where you alter the table and ...
0
votes
0answers
29 views

Complete a table automatically

I have a layer containing an id and a geometry. then I have 3 tables X, Y, Z connected by a relation of 1 to several. I will want to recover in Z for each new tuple the corresponding geometry, so as ...
2
votes
1answer
32 views

Getting “missing FROM-clause” error when adding ST_Intersects constraint to PostGIS table

I have two tables in PostGIS: afrivs, which is a polyline of various rivers, and weirs2 which is an empty polyline layer where potential dam sites will be added. Obviously dams have to cross a river ...
-1
votes
1answer
28 views

Why the replace function in PostgreSQL is not returning the value?

I am trying to convert state names to abbreviations and I am using the replace function. I am able to run the snippet successfully but names remain the same without being converted to abbreviation: ...
2
votes
0answers
37 views

Accessing PostGIS layers as non-data owner via QGIS?

I'm trying to create a database user (postgres) to access PostGIS data via QGIS. I've applied the following commands: GRANT ALL ON SCHEMA PUBLIC TO ene; GRANT SELECT ON ALL TABLES IN SCHEMA public ...
1
vote
0answers
42 views

Geometry type (Point) does not match column type (LineString)

I'm using PostGIS 2.5.2 on PostgreSQL 10, and I'm trying to build a function which would split a LineString into portions, depending on points positionned close to it. I'm getting a Geometry type (...
0
votes
0answers
12 views

Installing GDAL for Python having PostgreSQL already installed

I usually install GDAL following this instructions. Part of the process is to set up the GDAL_DATA environment variable to the path of the gdal-data folder of the previosly installed GDAL. Turns out ...
0
votes
0answers
23 views

GeoServer could not show the layer by SLD style

I am trying to show simple classification of semi real-time data by using PostgreSQL v.1.20, QGIS 3.4, and GeoServer 2.15.1 The idea is symbolized the field of 'Statusparkir' in table parkir_conn. ...
2
votes
0answers
69 views

Trigger PostgreSQL

I have a pb to make a trigger. I have 3 tables and a georeferenced layer: The common table contains: PKcom, INSEE The cadastre table contains: PKcadastre, Fkbat, section, parcel The building table ...
1
vote
0answers
32 views

How to speed up PostGIS intersects query?

I have a table which contains 5,00,000 records. I want to fetch all the rows which intersects with the given bounding box. My query is: select * from data_master b where b.geom && '...
0
votes
0answers
32 views

Does ST_GeomFromText find exact matches for provided string or it finds similar, but not 100% same string?

SELECT * FROM centroids WHERE centroid = ST_GeomFromText('POINT (21.6198205 41.9418615)', 4326); Record have centroid geometry equal to POINT (21.619820510769063 41.94186153740355) Running on ...
2
votes
1answer
80 views

Converting MULTIPOLYGON field stored as text to geometry data type in PostGIS and plotting in QGIS

I have this column Multipolygon which has data stored in this format which is in TEXT: MULTIPOLYGON(-115.528984981578 51.2006109579736, -115.526434407604 51.1974145241941, -115.546464451 51....