PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

2
votes
1answer
34 views

OpenStreetMap Data Lookup Slow in PostGIS

I have imported OpenStreetMap data (Europe) into PostgreSQL 9.1 database with the help of osm2pgsql and filtered out everything expect administrative areas. I have GIST index for polygon (way) column. ...
1
vote
1answer
78 views

Split a table of Multi/Linestrings into Unique Linestrings

Is there an efficient way to split a table of Multi/Linestrings that overlap() and touch() into unique Linestrings (note: creating edges using topology is going very...very...very slow). Here is ...
0
votes
0answers
12 views

How to render visualizations for geospatial objects (the way QGIS does it) on web interface for end users, from PostGIS database? [duplicate]

I have existing geospatial objects in PostGIS. I can visualize it using QGIS. But I want to render these visualization on a web-interface for my users as a result of some queries in the database. I ...
2
votes
2answers
51 views

geometry from relations - osm

Can someone show me the relevant documentation for relationships between tables? I'm trying to get the geometry for an entry from the relations table but I'm unable to pick out the right information ...
1
vote
0answers
40 views

PostgreSQL owner/role administration for QGIS projects

What's the best way to administrate PostgreSQL roles for QGIS projects? I've created a separte schema for each of my QGIS projects. Is it possible to revoke all privileges except "select" for finished ...
0
votes
1answer
36 views

Display NYC buildings where the tallest building on the block is above a certain height

There's probably a simple answer to this, but... I'm trying to figure out how to use the WHERE function to display only polygons (NYC properties, I'm using the PLUTO land use database) where the ...
0
votes
1answer
35 views

Moving file flat-nodes.bin from an osm2pgsql import

I'm running PostgreSQL 9.4 and osm2pgsql 0.82.0 on Ubunt 14.04 (64bit). I'm new-ish to postgres and less new to OSM. My goal was to import the planet file. Unfortunately, when I started the import, ...
1
vote
1answer
45 views

openlayers 2 coloring a polygon layer based on a database ( postgresql ) table that is different from the attribute table of the layer

i have trouble coloring the polygons in my layer based on the values in certain field in another table in my postgresql database that is different from the attribute table of the layer itself , is ...
0
votes
0answers
23 views

Related Spation and non-spatial in QGIS

I am working with QGIS 2.6.1 by adding data from server using PostGIS. Now, I want to see the attribute information. So, how can relate the spatial with the information?
0
votes
1answer
44 views

Add columns with information OSM by osm2po?

I imported the europa.osm network within my spatial database thanks to the osm2po tool. I use this with pgrouting. My problem is that I need to know the information provided by the tag address (city, ...
-1
votes
1answer
23 views

Rotate Polygon to be Parallel with the X Axis

By default, PostGIS calculates the Envelope or Extent of a polygon based on the bounding box of the polygon ((MINX, MINY), (MINX, MAXY), (MAXX, MAXY), (MAXX, MINY), (MINX, MINY)). This gives a result ...
3
votes
1answer
51 views

Poor performance with storing large rasters in PostGIS and visualising in QGIS

my question concerns the use and performance of several software tools in conjunction, namely PostgreSQL, PostGIS, QGIS, and GDAL. I'm a long-time ArcGIS, Python, and R user who is interested in ...
0
votes
0answers
17 views

Any experience or tips for using qgis with postgis layers? [duplicate]

we want to replace our shapefiles with postgis layers for editing in QGIS. We plan to edit 1 Layer with 5 people at the same time is this possible? Is this a good idea or is there a better practice ...
0
votes
1answer
44 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
27 views

How to reference to user input in SQL string in R?

I am building a R Shiny interface that queries the user input in Postgresql and generate the respective map layer. However, I am unsure of the syntax to include the query in the SQL string. my codes ...
0
votes
0answers
32 views

How to convert a geojson from Postgresql to geojson format readable in rCharts in R?

I am creating a Rshiny interface on that can take in user input and SELECT the respectively map layer from Postgresql database based on the input. The package I am using to plot the map is rCharts. ...
-1
votes
1answer
53 views

Adding a column of WKT format in the table (postgreSQL)

I want to include this column of data that are in WKT format (they are mostly straight lines). What data type should I use? I need to create this database for mapping. There are about 30000 features ...
0
votes
1answer
21 views

PGR_Djikstra function error

I'm trying to find a shortest path between 2 nodes using djikstra method but it keeps saying ERROR: Error, query must return columns 'id', 'source', 'target' and 'cost' this is my query: ...
1
vote
0answers
23 views

Why is arcpy ChangePrivileges_management adding a second feature class to map?

I am new to GIS and pretty new to python, so sorry if this is ugly code. I am creating a feature class in a postgres database if it does not already exist and the user wants it created. That works ...
0
votes
0answers
22 views

How do I tune postgres to quickly find the shortest path on a large dataset (4M)?

My graph has ~4M rows, and I am running it on 2.55GHz machine. When postgres gave it the default amount of memory settings, it took my ~55 seconds to run the pgr_ksp function against it (where k is ...
0
votes
1answer
78 views

Import point cloud into PostgreSQL/ pdal pipeline fails

I have a point cloud dataset that I would like to upload to my postgres/postgis database. I followed this tutorial and I could install everything without problems (on ubuntu 14.04. On ubuntu 12.04 ...
0
votes
0answers
25 views

pg_dump Database that was populated with osm2pgsql

Is it possible to dump a postgres database that was created with osm2pgsql and a .osm.pbf file with pg_dump and import this into a remote postgres database? After running osm2pgsql the database is ...
1
vote
0answers
66 views

Help serializing a postGIS query to geojson

I have a query which I would like to serialize to geoJSON: select article_title, pub_date, city, geom from article, location where article.id = location.id; I can serialize the geom field easily ...
1
vote
1answer
27 views

Simple popup for multiple WMS

I have multiple WMS in openlayers with a layer switcher and can be turned on and off. Now i have an image of each WMS, what i want is to show image of each WMS on click OF that corresponding WMS. ...
7
votes
1answer
146 views

How fast should I expect PostGIS to geocode well-formatted addresses?

How fast should I expect PostGIS to geocode well-formatted addresses? I've installed PostgreSQL 9.3.7 and PostGIS 2.1.7, loaded the nation data and all states data but have found geocoding to be much ...
0
votes
1answer
36 views

Utilizing OSM Data in a Postgres/PostGIS database in ArcMap

I've been trying for over a week now, and I'm not sure what i'm doing wrong and I haven't found the answer yet. I am trying to display and symbolize the OSM dataset over Jordan in ArcGIS for Desktop ...
1
vote
1answer
74 views

Number of Neighbors Around a Point

I need to get a count of neighbors within 100m for each point in a set of 250 million points. The result would have the id of each point accompanied by the number of neighbors around them. Can this be ...
0
votes
1answer
151 views

How do I explode a MultiLineString using ST_Dump() in PostGIS?

I want to do what this blog post does: explode a MultiLineString into individual LineStrings. I'm using CartoDB but don't get individual LineStrings as the result of my query. I still get a ...
1
vote
0answers
44 views

OSM Bright Cartography XML

So I've successfully setup a tile server as described here: https://switch2osm.org/serving-tiles/manually-building-a-tile-server-14-04/ What's troubling is the lack of clarify surrounding OSM Bright ...
0
votes
1answer
56 views

Foreign Key is not added to destination table after the creation of a relationship class

Let's say I have two tables A and B. The relationship between these two tables is as follows : A (1,*) <---- (1,1) B Now when I try to add a relationship class for these two tables on Arcatalog, ...
1
vote
2answers
29 views

Problem with MapServer WMS

Below code is showing the addition of mapserver file in openlayers 3, it is successfully showing me image in the browser but when i zoom that image it does not zoom. It comes back to originial view. ...
3
votes
1answer
47 views

Postgis query on 3D cube does not use index

I am testing Postgis on the follow query: SELECT * FROM particles WHERE position &&& 'LINESTRING(0.08537 0.0738 0.0145,0.0953 0.0837 0.0345)'; that returns about 100k rows from a ...
0
votes
1answer
37 views

How to cut contour lines at the edge of a data source

I have calculated some contour-lines using gdal-contour for two elevation model into a Postgis database. One is high res and the other lower res. Now I am seeing problems at the edge of my high ...
0
votes
0answers
35 views

How to arrange polygon in Postgis

I have a lot of polygons in a Postgres database. I want to show the polygons, but in order of the the biggest to the smallest. I used the following code: SELECT ST_Area(area_polygon) as luas FROM ...
1
vote
1answer
61 views

connecting Arc 10.3 to a PostGIS database “the coordinate system identifier is invalid” [closed]

Has anyone had any success directly connecting Arc 10.3 to a PostGIS/postgres database, where the geometry type is 'geometry'? I am able to connect and view the tables, but I am unable to view them - ...
1
vote
1answer
74 views

Matching GPS points to the road network

I am a newbie in Postgres and Postgis and I am trying to do a matching of GPS points to the road network. I need to find closest road from a shapefile and take speed attribute from it. I am using ...
1
vote
2answers
40 views

GIS and Geodatabase with Reminder Function

a friend of mine, a landscape gardener, asked me wether it is possible to represent his garden (mainly big trees and a few flower fields) in a GIS so he could administrate these on his computer by ...
1
vote
1answer
69 views

ST_Intersection for polygons

I'm looking for a query that returns overlapping areas of two tables with polygon geometries. Using row_number() over() the entries of the gid column are not unique in the case of dumped ...
1
vote
0answers
52 views

Auto-Sanitizing illegal PostGIS WGS84 (lat, lon) coordinates

I have just stumbled upon what seems to be a very curious behaviour by PostGIS. The XYZ point -117.0, 32.0, 0 in WGS84 (SRID 4326) is legal: items=> SELECT st_y(st_geomfromtext('POINT Z (-117.0 ...
1
vote
1answer
70 views

Determine XY offset in meters between two lat/long points (EPSG:4326), and accurately recreating point locations using XY offset

I have two tables in EPSG:4326, one for home locations and one for site locations. I need to be able to generate the XY offset in meters between the home location, and the site locations. I then need ...
0
votes
0answers
36 views

Speeding up PostGIS ST_AsGeoJSON

I am writing a query that would give me the geojson element contains the outline of a US State along with some additional information. The problem I am running into is the function ST_AsGeoJSON from ...
0
votes
0answers
23 views

libst_raster_pg.dll for PostgreSQL 9.2

I need to store my raster in an enterprise geodatabase with st_raster data type. Copying libst_raster_pg.dll from C:\Program Files (x86)\ArcGIS\Desktop10.3\DatabaseSupport\PostgreSQL\9.2\Windows64 ...
0
votes
0answers
22 views

Display graduated QGIS buggy?

I counted points in polygons in PostGIS like this UPDATE hexagons SET count = (SELECT count(*) FROM points WHERE ST_Within(points.geom, hexagons.geom)); That came back with that UPDATE 1554 That ...
0
votes
0answers
28 views

load raster (geotiff files) to postgis with url

I tried to load rasters to postgis with raster2pgsql but this not satisfy my project because it uses local files. i have 5000 and will be more in future geotiff files in cloud host. is it possible ...
1
vote
1answer
57 views

“Get” results of PostGIS [closed]

I am getting warm with post PostGIS. I am working in the Command Line. What I am wondering is how do I "get" the results of PostGIS. So imagine I have this select name, neighname from neighborhoods ...
1
vote
1answer
30 views

ArcSDE PostgreSQL configuration parameters for Spatial Index?

I wonder why there is not any configuration parameter (DEFAULT Keyword) for spatial indexes in Arcsde for postgres. In ArcSDE for Oracle we have S_STORAGE,S_INDEX_SP_FID,S_INDEX_ALL parameter for ...
1
vote
1answer
36 views

Raster and Geometry ST_Intersects slows when add buffer in PostGIS

I have a table containing rasters for the whole of the UK, the below query is very fast (0.1 seconds) and gets all of the rasters that are intersected by the line between a start point and an end ...
0
votes
0answers
42 views

Multiple LEFT JOIN in PostgreSQL

I got a "nice" problem with a query in PostgreSQL with a multiple join SELECT DISTINCT "Monitoraggio_L001_L008_2015_aprile"."ID", "Monitoraggio_L001_L008_2015_aprile"."Ordine", ...
0
votes
1answer
90 views

I have not found shp2pgsql in postgis installation

I have been installed postgis following this guide. All is OK. SELECT postgis_full_version(); "POSTGIS="2.1.7 r13414" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, ...
0
votes
1answer
65 views

select by location point in polygon with sql query

I have 2 tables table a this one a polygon layer with 10000 records table b this one a point layer with 600000 records in postgresql geodatabase i select a polygon feature with the query by input ...