PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

0
votes
0answers
21 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 ...
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 ...
0
votes
0answers
25 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 ...
0
votes
2answers
57 views

import OpenStreetMaps Data fails

I work in project of geocoding so i want to import the OpenStreetMaps Data. i created a database and name it nominatim and i tried this command: sudo ./utils/setup.php --osm-file ...
1
vote
1answer
23 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 ...
0
votes
1answer
29 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
1answer
654 views

Unable to load Shapefile using pgShapeloader / shp2pgsql-gui (pgAdmin3's PostGIS Shapefile Import/Export Tool)

I am trying to load a shapefile (http://dds.cr.usgs.gov/pub/data/nationalatlas/statesp020_nt00032.tar.gz) into PostGIS 2.0.1 using pgAdmin3's PostGIS Shapefile Import/Export Tool. However its not ...
0
votes
0answers
34 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
121 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
1answer
55 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
51 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 ...
3
votes
3answers
567 views

Visualization of a big graph in 3D from postgresql/postgis

We have a graph in 3D in a PostgreSQL/PostGIS database. It contains something like 50'000 edges and 50'000 vertices, with x-y coordinates and floors. We would like to visualize this graph with ...
1
vote
1answer
99 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
1answer
102 views

Point of intersection issues with PostgreSQL/PostGIS?

I have a table containing a set of points of intersections. Most of the roads intersects whith just one road BUT i have some roads which intersects with two, therefore three different point of ...
0
votes
0answers
32 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 ...
2
votes
2answers
77 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
32 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
35 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 ...
0
votes
1answer
42 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
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 ...
0
votes
0answers
71 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
73 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
1answer
41 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
0answers
32 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 ...
2
votes
1answer
273 views

How to extract a single road from OSM GIS Database?

Is there a way to extract a single row out of the OSM-GIS-Database (by a Query)? It would be so much easier if I could find an EER Model of the Database. I was searching a long term and found very ...
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 ...
1
vote
1answer
141 views

Install pgRouting on Postgres 9.1 and Win 32

I referred some documents on GIS-stackexchange and over internet regarding installation of pgRouting on Postgres v9.1 and windows 32. I am not getting the correct version of pgRouting to install. Can ...
0
votes
1answer
62 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
1answer
91 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
0answers
40 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 ...
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 ...
1
vote
2answers
258 views

Error inserting GeoJSON in PostGIS

I know there is a similar question but I am getting a different error and none of those answers have helped. I am trying the following query (the GeoJSON itself is from the PostGIS website. INSERT ...
2
votes
3answers
1k views

Spatial index to speed up the query

I have spatial table in PostgreSQL 9.2 / postgis 2.0 that I have to extract some information from it. But my query is hopelessly slow. How could I make use of spatial index to improve the performance ...
2
votes
4answers
159 views

PostGIS doesn't use spatial index with ST_Intersects

I imported the data from openstreetmap using osm2pgsql and copied it another table structure. If I made a query for containing rows using st_intersects, st_contain or someone else, postgis don't use ...
1
vote
1answer
55 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 ...
2
votes
1answer
69 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 ...
0
votes
0answers
18 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 ...
0
votes
2answers
50 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 ...
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 ...
2
votes
1answer
102 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 ...
2
votes
0answers
43 views

How do make ST_Split ready for QGIS?

In my recent QGIS project I need to split polygons (table/layer "pol") by both closed and non-closed linestrings (table/layer "lin"). I've tried the following SQL queries. CREATE VIEW split_pol AS ...
10
votes
3answers
7k views

Error when trying to run Create Extension postgis

I have installed PostgreSQL 9.x and PostGIS 1.5/2.0 several times and have never had this problem. I just got a new CentOS 6.3 server up and running and and I have Postgres 9.3 functioning as ...
1
vote
1answer
52 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 ...
3
votes
1answer
224 views

How to edit a PostGIS database using ArcGIS for Desktop Add-In/Extension? (no ArcSDE)

I need to improve an existing solution to be able to do multiuser editing within a PostGIS database from ArcMap. Available for the solution is ArcGIS for Desktop 10.3 [Basic]. (No non-free Extensions ...
0
votes
0answers
19 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
38 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
19 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
18 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 ...
0
votes
0answers
23 views

Defining 'os' in geonode custom installation?

Can any body teach me how to resolve this GeoNode problem? I tried following Geonode's documentation on installing it in development mode which was successful however when I tried changing its default ...
1
vote
1answer
48 views

How to concatenate values from postgis table which have the same id into a new column? [closed]

I have the following table in postgres-9.3 postgis 2.1. (geom values is too long , I cut to make it short in below table): id | name | geom ------------------------- 12 | A | "01030000000" 12 | ...