Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

680 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
1
vote
0answers
2k views

Geometry contains non-closed polygon: GeoServer output from PostGIS

I have imported a shp file using PostGIS shapefile loader plugin for pgAdmin3. The geometry type of the table created is MultiPolygon. I have cross checked the same using the following query. SELECT ...
1
vote
0answers
390 views

Live KML Feeds - From the Ground Up, How Would You Build It and Why?

I found the "Live KML from PostGIS to Google Earth" post very informative but a bit daunting for someone new to the open source world of products like PostGIS and GeoServer. If I am building the ...
1
vote
0answers
110 views

Getting started with storing LoST and GML searching

I am working on a logging and search database application. The application will receive event records containing XML LoST values (RFC5222 Location-To-Service-Translation). These records will be ...
1
vote
1answer
47 views

Cannot convert shapefile data to PostgreSQL/PostGIS using ogr2ogr

I am a starter to use command-line interface. I was looking for a way to import a shapefile into PostgreSQL using "ogr2ogr". I was able to do this for point shapefile using code: ogr2ogr -f "...
1
vote
1answer
57 views

Postgres ST_Distance_Sphere Runtime Improvements

I need to select all entries from a table, that are within a distance from a point. Each entry is a point geometry (I set a gist index on geom). Current runtime is anywhere between 3-15 seconds, ...
1
vote
1answer
502 views

How to search ST_MakePoint(long,lat) in all the geometry columns of tables in the database(postgres)

I want to search a particular given given lat,long within all the shapefiles geometry columns of all tables in the database . I have gone through the scripts like below CREATE TABLE #Results (geom) ...
1
vote
1answer
99 views

I am using an example fracnsites to make a similar map but I don't under stand the ajax code?

I am using an example fracnsites to make a similar map but I don't under stand the ajax code? The example has js code: var map, fields = ["gid", "createdby", "featname", "feattype", "status", "acres"...
1
vote
1answer
728 views

Error when enabling ArcGIS geodatabase

We're trying to enable enterprise geodatabase functionality in an existing PostgreSQL database. We've already created the sde user (set as superuser) & sde schema. We get the following error: "...
0
votes
0answers
7 views

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

I have used postGIS's raster2pgsql to export 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 ...
0
votes
0answers
3 views

How to manage different srid data in same database table?

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
0answers
10 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 ...
0
votes
0answers
14 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
17 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), ...
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 ...
0
votes
0answers
16 views

Connection between GeoNetwork and PostgreSQL

I want to migrate to GeoNetwork 3.10.2 from 3.8 by following those instructions : BACKUP my DB PostgreSQL I change these files in order to point my new DB C:\Program Files\Apache Software Foundation\...
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 ...
0
votes
0answers
20 views

WFS Request against a layer with a foreign table join

I have a Geoserver layer that is pulling from a Postgresql view that contains a left join to a foreign table (data from a SQL Server database). If I query against the view in PGAdmin (ie. SELECT * ...
0
votes
0answers
28 views

How to plot postgres data on a map?

I have a table which has latitude, longitude and some sensor data (data 1, data 2,data 3) saved in postgresql 12. I am running the database on ubuntu 18 virtual machine with windows as host. I dont ...
0
votes
0answers
30 views

Connect GeoServer to ESRI PostgreSQL database

I want to connect to an Enterprise geodatabase in PostgreSQL from GeoServer. The ESRI GDB is not running a SDE instance. Can I do a direct connection from GeoServer? What is the right way to put the ...
0
votes
0answers
56 views

Optimising Cross Join PostGIS Query

I have tried performing the query shown below via ogr2ogr and the gpd.GeoDataFrame.from_postgis() method in geopandas. from osgeo import ogr import psycopg2, os import geopandas as gpd import pandas ...
0
votes
0answers
11 views

ArcMap - Definition query using dates to select current week

I would like to write a definition query expression to get features where a date field is in the current week (starting Sunday ending Saturday) I have difficulties to imagine how using CURRENT_DATE ...
0
votes
0answers
17 views

Losing connection to the server when using PG Routing function pgr_alphaShapes

I am running PG Routing v. 3.0 on Postgresql 12.2 and the pgr_alphaShapes function is giving me a lot of trouble. Following the documentation here, I passed a geometry and a spoon radius. SELECT * ...
0
votes
0answers
53 views

QGIS, PostgreSQL, user and roles

I am trying to create a shared server for community, and now stock=k with users privilege and roles, I get an error on QGIS when login with my user account. Steps I did : I revoked all privileges ...
0
votes
0answers
69 views

Strange PostGIS errors after having altered its extension by specifying the postgis schema

Context I'm trying to refine a PostgreSQL (v.10.12) trigger in order to actually execute some computations on point features only if the input points do intersect a convex hull stored in an other ...
0
votes
1answer
29 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 MUTIPOLYGONZM and I want to insert the data into one
0
votes
0answers
66 views

Selection on PostGIS layers is… randomly working in QGIS

Context Quick question... Since the last minor update of QGIS 3, I cannot select features anymore on some (not all) PostGIS layers (see Fig.1). Fig.1: On the left table, the selection is not ...
0
votes
1answer
11 views

Trouble working with a .TSV containing WKT

I'm working with a .TSV file that represents district boundaries in a portion of a country in Carto. The polygon data is represented in a string column of Well-Known Text lat/long vertices, which I'm ...
0
votes
0answers
11 views

How to install pgRouting and use it in pgadmin4 in OSX

Is there a straightforward way to install pgRouting in my existing PostgreSQL(version 11) installation and use it through my pgadmin4 by creating an extension? I am using OSX.
0
votes
1answer
23 views

Get all rows that have geometries which intersect with a geometry in any row of a different table in an effective way

I have two PostGIS tables containing thousands of geographic polygons each (each row has one polygon and numerous other attributes). Both tables represent different data sets but cover the same area ...
0
votes
0answers
30 views

Missing gml_id when using ogr2ogr from WFS to PostGIS / GPKG / GML

I am trying to find the way to download on our PostgreSQL+PostGIS database the content of several WFS at regular intervals. It has to be done at regular intervals so we want to the whole thing to be ...
0
votes
1answer
39 views

How to update tables from QGIS to pgAdmin?

I have created a connection between QGIS (ver. 3.10.1-A Coruña) and PostgreSQL (pgAdmin 4). Every time I edit/update my table in QGIS I have to delete the table in PostgreSQL and re-import again the ...
0
votes
0answers
43 views

How to modify the columns to geometric points

How make points geometric if already exist columns with data like column names are gps_latitued and gps_longititude in mapping table
0
votes
1answer
18 views

PGRouting Error Missing table?

I am trying to use pgrouting to find the shortest path in GeoServer. So I created new layer in sql view but its giving me error. I created 3 table in database: at_2po_4pgr = main table ...
0
votes
0answers
40 views

Inserting X,Y to Geometry in Spring Boot and PostgreSQL?

I use @query to Insert X,Y to Geometry in PostgreSQL. @Modifying @Query(value = " insert into mypoints (name,geom) values (:name,SRID=4326;POINT(:x :y))", nativeQuery = true) @Transactional void ...
0
votes
0answers
16 views

OBDC Error While Exectuing the Query #7 - Importing PostgreSQL table into MS Access

Trying to import a table from PostgreSQL to MS Access (2007) however, I keep getting the error OBDC Call Failed - Error While Executing the Query #7 STEPS TAKEN In OBDC Data Source Admin (32-bit)...
0
votes
0answers
11 views

Building PostgreSQL 12.2 and PostGIS 3.0 on MacOS Mojave with GCC - Could I use Clang LLVM compiler?

I need to deploy PostgreSQL 12.2 and PostGIS 3.0 to a Mac Pro server for Tiger and OSM tiles to provide a GIS Developer workstation. I've done this before several times but never successfully built ...
0
votes
0answers
43 views

Restore a PostGIS database cluster properly

Context In order to migrate from PostgreSQL 10.12 to 12.2 on a Ubuntu 18.04.4 LTS server, I first uninstall and purge all postgres related stuff then I followed this; https://www.postgresql.org/...
0
votes
0answers
31 views

Converting POLYHEDRALSURFACE to (MULTI)LINESTRING or POLYGON?

99% of my table are MULTILINESTRINGs, but for some reason a few hundred are stored as POLYHEDRALSURFACE. How can I convert the following into MULTILINESTRING, LINESTRING, or POLYGON? ...
0
votes
0answers
39 views

Loading raster on PostgreSQL/PostGIS Linux server not working

I used the following raster2pgsql.exe to upload an SRTM mosaic to my Linux database server (working fine for vector data): raster2pgsql.exe -I -t auto e:\data\Sinai\SRTM90V41b\SRTM41M90.tif SRTM90c | ...
0
votes
1answer
86 views

Problem with clob data type when running ogr2ogr command

Using ogr2ogr, I transferred data from the oracle database to the postgres database. However, the table "table" contains attributes that have a "clob" as a data type, which were not created. Is there ...
0
votes
1answer
42 views

Alternate of intersects ?# operator in postgres

Is there any way to find if a path is intersecting with a box in postgres without using ?# or using an alternative? I have this query below and hibernate's EntityManager is throwing an exception ...
0
votes
0answers
30 views

Raster2pgsql turning raster values from Float32 to Byte

I would like to add netcdf files to a postgreSQL database. I'm running the command: raster2pgsql -C -I -F -s 4326 test.nc | psql -h localhost -p 5433 -U postgres -d UV It adds them successfully but ...
0
votes
0answers
28 views

Clean local OSM website database but only the geographical data

I also posted this question at help.openstreetmap 2 weeks ago but got no answer, so I hope someone here have the answer. I ran ./osmosis --truncate-apidb in my local OSM website to clean the data, so ...
0
votes
1answer
113 views

Loading layers into QGIS - Heavy work in the background - Too many clients already

I am 3rd year geoinformatics bachelor student currently developing a QGIS plugin that will allow me to import a larger number of layers from the data storage (Postgres) into the software. For the ...
0
votes
0answers
22 views

Importing external data (GeoServer) to ArcGIS Web AppBuilder

I'm starting a web app using ArcGIS Web AppBuilder (WAB), I want to import my data without using ArcGIS Server, from GeoServer or PostgreSQL, I found this Widget LocalLayer and it looks so great for ...
0
votes
1answer
27 views

Local OSM server query

I need to make a query for my local OSM server which returns a table with 2 columns: user.display_name and the total of nodes of the ways with tag k='waterway' created, edited or deleted by that user. ...
0
votes
0answers
28 views

Import a shapefile into PostgreSQL using npgsql (.NET)

I want to import a shapefile (.shp) into a PostgreSQL database through a .NET application. I plan to use npgsql but I am unable to figure out how to import a shapefile using this library. PostgreSQL ...
0
votes
0answers
31 views

QGIS 3.12: Importing shape (point) to PostGIS database as points without Z values

Currently I am importing vector layers (points) to a PostGIS database via QGIS 3.12. Everything works great expect for one part: after the import is complete all my points have a Z value (which is ...
0
votes
0answers
22 views

Write a PostgreSQL extension that makes use of PostGIS internal methods?

As I've just found out, it is possible to extend PostgreSQL with custom functions written in C. And I also now know that that's how PostGIS itself is written. However what if I want to build on top of ...
0
votes
0answers
36 views

Problem with importing VFK into PostgreSQL with ogr2ogr

Problem is importing VFK (Czech cadastrial data format) into PostgreSQL by ogr2ogr. Versions are: PostgreSQL: 12 PostGIS: 2.5.3 GDAL: 3.0.2 Command is: ogr2ogr -overwrite -f "PostgreSQL" PG:"...