PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

1
vote
1answer
186 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
90 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
758 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
87 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
142 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, ...
2
votes
1answer
72 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
92 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
71 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
42 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
247 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
187 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
54 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
123 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
94 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
120 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
66 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
63 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
25 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
84 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
61 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
63 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
85 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
192 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
2answers
912 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
178 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 ...
1
vote
1answer
759 views

PostgreSQL: insert from multiple select?

Is it possible in PostgreSQL to insert field values into a table using multiple selects from various other tables? Something like the following SQL (I have over simplified my original problem). I ...
1
vote
0answers
66 views

OS Terrain 50 into Postgresql/PostGIS for lat/long analysis

I am performing analysis using line of sight's and followed a very useful blog post at http://davidrowley.blogspot.co.uk/2013/08/calculating-line-of-sight-with-postgis.html which detailed how to load ...
0
votes
1answer
42 views

moving user by gps and show interested points

Is it possible in a Web application with google maps, to use a user his mobile gps and when move and is near on an interest point such as hotels, interested point i have stored in a Postgresql ...
2
votes
2answers
178 views

Why the execution of a query is very slow using PostGis?

I'm applying this Query to 8257800 point but it's taking 15 hours and it didn't return a results. SELECT a.gid , b.gid, st_distance(a.geom,b.geom) FROM shp1 a, shp2 b where ...
1
vote
0answers
63 views

Importing tif image from PostgreSQL Database to webpage

I am trying to import tif images from PostgreSQL Database to my website. In the website, I need to make a button control which will open the images. I think I have to use Python as a server side ...
0
votes
1answer
86 views

Create blank table from unix shell

I am in the middle of trying to create my first PostGiS database and seem to be running into problems developing scripts to automate importing the various spatial formats so I can use them in my ...
0
votes
0answers
245 views

Import OpenStreetMap to shapefile in ArcMap

I would like to import an osm file to a postgresql in order to be able to extract soe information and convert it to a shapefile. I have downloaded the PostgreSQL. My question is what are the main ...
1
vote
0answers
160 views

Connect to PostGIS database from Q GIS

I am having trouble connecting to a postgresql database hosed on my local host. I have gotten a python script to connect and connected through the command line, but for the life of me cant connect to ...
0
votes
0answers
46 views

OSM2PO get all nodes included in a segment

We have been working with osm2po tool to load data to a Postgis Database. We would need all the nodes included in a segment not just the source and target node. Is there any way to change the ...
0
votes
1answer
59 views

how to insert coordinate of province to postgresql?

i need insert to database this area sample: [ [ [ 50.227886556627098, 31.15709614260982 ], [ 50.227886335531331, ...
0
votes
1answer
29 views

Creating network for pgRouting from set of multiline strings?

I have a table which is a set of multiline strings. I wish to create a pgrouting topology from that table so I can do shortest-path problems. I have tried pgr_createTopology but it failed. Is ...
0
votes
1answer
127 views

Database queries using GeoServer? [closed]

I am new to GeoServer. I have a PostGIS database and GeoServer setup. How can I query the database using the GeoServer/ Boundless SDK? I have OpenGeo Suite 4.6 and I am trying to query the database ...
2
votes
2answers
165 views

Merging linestrings in QGIS using PostgreSQL and PostGIS

I've to merge all linestrings from a PostgreSQL table "river" where not more than two start-/endpoints are overlapping. In the illustration below I use a "M" to mark all linestrings that needs to be ...
2
votes
1answer
230 views

How to efficiently use a Postgres DB with Python

I have a Postgres DB with a lot of lines that are too long and so now I want to split those lines into smaller ones. This is why I started writing a little python script for that job. Basically it ...
1
vote
2answers
383 views

Dynamic connection between Mapinfo and a database

I'm trying to create a dynamic connection between a database and a Mapinfo table. My database countain only one table, with a list of bus stops, characterized with their ID, name, and X Y coordinates. ...
0
votes
0answers
49 views

pgRouting pgr_trsp returns no route

Installed a fresh PostgreSQL v9.4.1 x64 on Windows, and used the Application Stack Builder to install PostGIS 2.1 bundle which includes pgRouting 2.0. I created the sample data ...
1
vote
0answers
34 views

Export only one value from a one-band raster

I have a PostGIS database with a georeferenced raster, with one band which contains values from 0 to 255. I should export it to a transparent GeoTiff picture in which the only visible pixels are those ...
0
votes
2answers
74 views

Count number of points within certain ranges from a set of points

I have the following, which gives me the number of customers within 10000 meters of any store location: SELECT COUNT(*) as customer_count FROM customer_table c WHERE EXISTS( SELECT 1 FROM ...
0
votes
1answer
29 views

Get percentage based on counts

I have the following query which gives me two columns store and customer_count...one row per store. SELECT a.id AS store,count(b.*) AS customer_count FROM stores a, customers b WHERE ...
4
votes
1answer
223 views

Find number of points within certain radius of a set of points

I have two tables. One is a list of stores (with lat/long). The other is a list of customer addresses (with lat/long). What I want is a query that will return the number of customers within a certain ...
0
votes
0answers
56 views

KNN combined with other clauses

I have a table with columns:- wkb_geometry (geometry), road_id character varying, year INTEGER The data set has multiples of road_id across many years. I want to get the nearest 10 values to a ...
9
votes
2answers
254 views

How to ST_Split features in one table by features in another?

I need to split polygons (layer 'pol') by both closed and non-closed linestrings (layer 'lin'). Unfortunately I don't get proper results running the following query. CREATE VIEW splitted_pol AS ...
-1
votes
1answer
67 views

How to import data into a table from another similar table?

I am working with PostgreSQL to import a table from a .shp file and my task is to populate another table (same attributes as this .shp table) with the data of the table of the .shp file table that i ...
2
votes
1answer
82 views

Is there a way to improve the time for opening a QGIS project?

We have a QGIS project using postgresql database "made with" osm2pgsql. The database is about 68GB. Our Configuration is : AMD-FX 8 cores @ 4.7Ghz 32 Gb RAM 500 Gb SSD (read/write 600Mb/s) Ubuntu ...
1
vote
1answer
63 views

Can QGIS manage non-spatial PostgreSQL database?

I have a database with a table with plots. This table has spatial field for the area. Also, I have another table with owners. An owner can own some plots, and the there are a "id_owner" field in the ...