Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

Filter by
Sorted by
Tagged with
0
votes
1answer
427 views

ogr2ogr -sql command when appending to PostgreSQL database: what functions are available?

I am trying to append a set of polygons from a shapefile to an existing table in a PostgreSQL/PostGIS database. The destination table only has a geography column and an 'attributes' column with jsonb ...
0
votes
1answer
183 views

How to publish a postgis layer to geoserver?

Now my geoserver is on the amazon instance and the postgresql is on the other Amazon RDS. I have try the way provided by official document, but it always show the errors as following: "Caused by: ...
0
votes
1answer
1k views

Topology exception on geometry intersection?

I am using the following PostGIS functions in a Postgres query: ST_AsGeoJSON(ST_Intersection(p.geom,c.geom)) Where p.geom & c.geom are type multipolygon. The query works most of the time but ...
0
votes
1answer
426 views

indexes to optimize ST_Contains and ST_Overlaps for geoms in two tables

I'm a programmer getting into the wonderful world of GIS. I just had a fun day optimizing queries (took one from 60s to 3.4s, and another from 38s to 1.2s). Below is the last query that I can't seem ...
0
votes
2answers
210 views

PostGIS query works as SELECT but not UPDATE

I want to clip the geometries of my table by a polygon. I have a query that does this just fine as a SELECT query: WITH clip AS ( SELECT ST_GeomFromEWKT('SRID=26915;POLYGON ((379308....
0
votes
1answer
91 views

What information is stored in a PostGIS geometry and how can I access it?

Consider the following queries: SELECT ST_GeomFromEWKT('SRID=32632;POINT(10 20)')::varchar UNION ALL SELECT ST_GeomFromEWKT('SRID=4326;POINT(10 20)')::varchar; UNION ALL SELECT ST_AsEWKT(...
0
votes
1answer
114 views

How to update a buffer view with values from another table?

So basically there's a buffer view defined by: CREATE OR REPLACE VIEW niceview2 AS select 'test'::text AS route_type, st_setsrid(st_union(st_buffer(a.geom, 25::double precision)), 27700) AS geom ...
0
votes
1answer
545 views

error on st_intersects

I have this query where I'm trying to two join tables with st_intersects. There will be different geometry types in the workarea column but the point_geom will always be a point. with cte1 as ( ...
0
votes
1answer
164 views

Join edge table on pgRouting trsp()

I am trying to figure out why pgr_trsp() isn't giving me the interpolated route (start and end positions are the full line instead of somewhere along that line). I believe it has to do with the way I ...
0
votes
1answer
339 views

Route starting from closest node along edge

I am trying to find a way to route from the closest point on a linestring to my current location (lat, long). So far I am able to get the shortest path but it starts from the very beginning of the ...
0
votes
1answer
256 views

Using PostgreSQL functions in a QGIS Plugin

I'm planning to write a QGIS plugin which calculates isochrones. I have created an tested a network in PostgreSQL and now I want to create a plugin. The Idea is to use a point layer as input and ...
0
votes
1answer
184 views

Slow spatial join on many tables

I try to create a view in an Imposm database for rendering purpose. This view is merely a copy of the osm_resorts table, but with another column members_types that concatenate all the type strings ...
0
votes
1answer
190 views

ST_AsGeoJSON providing unexpected values

GIS newbie here! I have been playing around with the import I did from an OSM region (that I got from here) to postgres using the osm2pgsql conversion tool. The command I used to import was this: ...
0
votes
1answer
133 views

Publishing tables from lots of different databases on GeoServer?

I have a pretty similar question compared to this one: Publish multiple tables from different PostgreSQL's schemas on GeoServer The main difference is the amount of databases. Question above has ...
0
votes
1answer
399 views

Extract points/pixels from raster based on % of intersection using PostGIS and PostgreSQL

I need to overlay a geometry (Shapefile of some city in UK) over a raster (grids of entire UK Map) and extract cells/pixels from raster that intersect with points of geometry. Current Implementation:...
0
votes
1answer
474 views

Connecting Django to postgresql

I plan on building a web application using Geodjango. After installing PostgreSQL, PostGIS, OsGeo4W and psycopg. I changed my settings.py db to that of the PostGIS. Each time I run the commmad "python ...
0
votes
2answers
356 views

PostGIS & Python SQL Query formatting: Create Geometry only for entries that are not NULL

I think this is rather a formatting issue. I have a database with a geometry Column that sometimes contains points. Normally, it will be None/Null. When I format the SQL query in psychopg2 and python ...
0
votes
1answer
120 views

How to merge datasets without creating new columns

I am using Carto Builder and I have two datasets with exactly the same column headings but each dataset has different data under each column heading. Is there a way to merge the data without ...
0
votes
1answer
51 views

Query to obtain all ends of a tree

I have this sample of a tree with the table 't_cheminement' : My schema is a table with : letter, startPointNumber(cm_ndcode1), endPointNumber(cm_ndcode2) and the geom. I have some issues when I ...
0
votes
1answer
110 views

Change layer style in database

I stored the styles of my layers in my pgsql database, and there is a need to change the style of a feature if a certain field is updated. I'm wondering if there's a possibility to set the ...
0
votes
1answer
84 views

How to pull data from an existing PostGIS database into Carto?

I have an existing PostGIS database. I want to be able to access my tables from that DB on my Carto enterprise account. Any suggestions?
0
votes
1answer
236 views

Adding map current location(Latitude and Longitude) in ViewParams of Geoserver WMS using Leaflet

I am drawing Geoserver layers on google map using Leaflet. Base database for Geoserver is PostgreSQL.In PostgreSQL there is a table that stores Millions of polygon.These polygons are in the form of ...
0
votes
1answer
213 views

PostgreSQL+PostGIS creating GeoJSON with ST_MakeLine

Trying to get a valid geojson response from my PostgreSQL(9.6.3) + PostGIS (2.3.2) to track routes. I have a table of GPS positions of the form: CREATE TABLE public.gps ( id integer NOT NULL ...
0
votes
2answers
497 views

pgAdmin III SQL creating a point

I have created a table, dc_crimes, that has eight columns. I would like to fill the geom column with point data created from the lat and long column. The syntax I used to create the table is: CREATE ...
0
votes
1answer
595 views

ogr2ogr shapefile to PostgreSQL problem

Trying to use ogr2ogr to import a shapefile in PostgreSQL. Here is my command: ogr2ogr -append -f "PostgreSQL" PG:"host=localhost user=postgres dbname=routing " C:\Users\rizagha\Documents\...
0
votes
1answer
46 views

Testing for one or more intersections

I am interested in creating a list of all the IDs from Table_A and whether or not they intersect with any of the geometries from Table_B. The following would work: SELECT Table_A.id, bool_and(...
0
votes
1answer
204 views

dissolving polygon records that only fall completely in another polygon from the same table

I have a polygon table that consists of 700 records with the subtype of 'OutdoorRecreationalFacility'. this postgres table is supposed to be a table of 1000 foot buffers around certain parcels. ...
0
votes
1answer
218 views

Error while importing geotiff file into postgresql database in linux platform

I am trying to import GeoTIFF file into spatially enabled PostgreSQL database. Using command: raster2pgsql -s 4326 -C -F -a path/to/geotiff/file public.table_name | psql -d database_name -U ...
0
votes
1answer
286 views

PostGIS: calculate intersection, then split intersection by attribute?

I'm using Postgres 9.6 and PostGIS 2.3.2. I want to find the intersection of two tables, then the area of the intersection for each of the attributes from the first table. My first table is called ...
0
votes
1answer
276 views

Importing a shapefile with no specifed projection to Postgres?

I am working with a shapefile that does not come with a .prj file - only .shp, .shx and .dbf files. The file's accompanying README says: ALL DATA IS IN A GEOGRAPHIC COORDINATE SYSTEM (NOT PROJECTED) ...
0
votes
1answer
215 views

Lizmap editor won't save edits to pgsql

So I have successfully installed Lizmap, Qgis-server, Apache, and a BigSQL postgreSQL database and published a map with a working WFS pointed at a PostGIS enabled table in my database. When I try to ...
0
votes
1answer
78 views

Error using Postgis 2.3.1

I installed PostgreSQL 9.6, PostGIS 2.3.1 and Pgadmin III on my computer, and I was reading the documentation, so I thought that would be a great idea see the function on practice and then modifie ...
0
votes
1answer
944 views

PostGIS - geocode and address standardization

Can you please help me ( I am not professional, If it is possible some easy explanation) how to proceed with address standardization and geocoding with Open Street Maps data (outside the US) in ...
0
votes
1answer
313 views

How to hierarchically query PostgreSQL data coming from OpenStreetMap

I have loaded OpenStreetMap data into a PostgreSQL database using Osmosis. This is done by using the snapshot schema. I filtered the data and loaded only: country & city information osmosis --...
0
votes
1answer
102 views

How to run ESRI CreateEnterpriseGeodatabase python script

I am trying to run the Python script posted by ESRI for generating an enterprise geodatabase from PostgreSQL using the following command: python create_enterprise_gdb.py --DBMS=POSTGRESQL -D ...
0
votes
1answer
145 views

How fill the vias_path field for restriction table

In documentation explain the restriction table for pgr_trsp function, and that way I can restrict left turn or right turn {target_id, from_edge}. But if I want to restrict multiple links it doesn't ...
0
votes
1answer
195 views

Saving shp data to PostgreSQL database?

I have a PostgreSQL database with multiple schemas. I am trying to save data using Arcpy to my database but I cannot figure out how to define the schema I want it in. The postgres.sde database has 3 ...
0
votes
1answer
399 views

Creating Postgis Polygon using ST_ConcaveHull from Geometry Points

I have a postgis 2.2 table with 20 columns of type geometry(Point,4326) I'd like to generate a polygon which covers the outer boundary of the points - it seems like ST_ConcaveHull is a good option, ...
0
votes
1answer
115 views

PostgreSQL table corrupt not display in QGIS

I have a problem with a PostgreSQL table. It is the result of a query that joins 77 tables with 4 columns (geom, inicio, fuente, temporada). I used ST_SRID and ST_Transform because I could not join ...
0
votes
1answer
688 views

Seeking solution to using PostgreSQL/PostGIS views in ArcGIS for Desktop?

We are currently trying to 'clean up' our geodatabase. For example: we have 30 datasets of districts, each of a different year (1986-2016). To make it more visible, we want to combine them all into 1 ...
0
votes
1answer
3k views

ERROR: syntax Error At or Near “update”

I am trying to create a new column in my parks database then calculate the area of the park but i keep getting this syntax error. Can anyone see what is wrong with it? alter table parks add column ...
0
votes
1answer
504 views

error creating Geoserver layer from Multipolygon PostGIS table

I imported the table from a multipolygon shapefile using PostGIS Shapefile and DBF Loader 2.2. I tried to use the 'generate simple geomtries ...' option in the tool, but got back an error : 'We ...
0
votes
1answer
254 views

raster2pgsql error: violates check constraint “enforce_out_db_rast”

When I run the command: raster2pgsql -s 4269 -a -R -F -t auto path_to_my_file.tif public.agdd_2016 | psql -U postgres -d climate -h server_ip -p 5432 --pass I get the following error: Processing 1/...
0
votes
1answer
131 views

Drawing markers with custom SQL in javascript with carto

I am trying to draw markers with custom psql using the js cartobd.createLayer method. The below snippet doesn’t throw any errors, but doesn’t draw any markers on the map either. window.onload = ...
0
votes
1answer
1k views

How to include filename when importing txt / csv file in postgres? [closed]

When I import a csv-file in postgres using psql I would like to add the filename in a column (before each row). Is this possible? The command I am using: psql.exe -h host-U user -d database -c "\...
0
votes
1answer
346 views

OSM postgis radius search

I imported the OSM data into my Postgresql 9.5 database, I also installed the Postgis. So when I use these SQL-Syntax: SELECT ST_Y(ST_Transform(point.way, 4326)) AS lat, ST_X(ST_Transform(point.way, ...
0
votes
1answer
2k views

Error to create postgis extension

when creating the postgis extension CREATE EXTENSION postgis; I have the next problem ERROR: Could not access the file «$libdir/postgis-2.2»: No such file or directory I use Ubuntu 14.04.5 ...
0
votes
1answer
49 views

PostGIS to Find Dupes and Segregate Geometric Dupes from Originals?

I've written some code in Python and PostGIS to find duplicate geometric features within the same feature layer. That part is a success. The problem I have is the dupes as well as the original ...
0
votes
1answer
792 views

How to do an Overall Minimum Bounding Rectangle from a Python List of MBRs?

I've converted an ESRI GDB file over to a PostgreSQL DB and went through all the layers now expressed as tables. I originally looked here but realized that having ~ 49 tables / layers would get hairy:...
0
votes
1answer
282 views

I have tried gdal builidng from source and rpm and still get the following error trying to create the postgis extension

create extension postgis; ERROR: could not load library "/usr/pgsql-9.5/lib/rtpostgis-2.2.so": /lib64/libgdal.so.1: undefined symbol: atom_obj_cache

1
57 58
59
60 61
67