PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

1
vote
2answers
32 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
56 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
44 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
36 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
99 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
107 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
43 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
78 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
57 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
81 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
39 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
31 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
23 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
41 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
59 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
32 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
42 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
50 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
143 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
80 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
194 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
40 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
38 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
96 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 ...
0
votes
0answers
41 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 Pyhton as a server side ...
0
votes
1answer
55 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
133 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
107 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
38 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
45 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
22 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
75 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
118 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
113 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 ...
0
votes
2answers
170 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
27 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
28 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
53 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
25 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
143 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
53 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
208 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
37 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
75 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
54 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 ...
1
vote
1answer
58 views

Running spatial query on huge table [duplicate]

I've got two tables conatining geometry: 1 containing big polygon's (regions, srid=4326) and another containing smaller polygon's (pand, srid=28992, containg ~9.9milion rows) By using one of the big ...
0
votes
0answers
27 views

Tilestache - Mismatch between the coordinates present in the PostgreSQL/PostGIS and those returned by PostGeoJSON provider

This is the relevant bit my Tilestache config, "points-of-interest": { "provider": { "class": "TileStache.Goodies.Providers.PostGeoJSON.Provider", "kwargs": { "dsn": ...
0
votes
1answer
43 views

SQL query giving error in pgrouting?

SQL error: ERROR: function point(numeric) does not exist LINE 4: (ST_GeometryFromText(Point(53.34972 -6.26025)), 4326), 1000) ^ HINT: No function matches the ...
0
votes
0answers
32 views

Get addresses from TIGER geocoder in polygon

I have the TIGER geocoder installed, setup and working in POSTGIS. I also see the reverse geocoder lets me get an address from a point on the map. But now I want to provide a polygon and get all the ...
0
votes
0answers
22 views

sql transformation from 29902(tm65) to 4326(wgs84)

ALTER TABLE Counties ALTER COLUMN geom TYPE geometry(Point,4326) USING ST_Transform(geom,4326); gives me the error: SQL error: ERROR: relation "counties" does not exist In statement: even ...