PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

1
vote
1answer
185 views

counting overlapping polygons - ST_Union very slow, how to make it more efficient?

I have a set of 1000 polygons that overlap and I want to count the areas of overlap. I am using this blog post's instructions to create multilines from the 1000 polygons then use this line file to ...
0
votes
2answers
409 views

PostGIS: Closest polygon to point [duplicate]

I have .shp file of polygons and single (point) location. How can I retrieve the closest polygon to that single location? Thanks,
0
votes
1answer
37 views

Which is the correct Options 's “Hydraulics” field in EPANET?

Which parameter has to be given into Options 's "Hydraulics" field in EPANET? Has it to be only a name or a complete file name with the extension? In my case .rpt doesn't work, neither without the ...
1
vote
0answers
36 views

How raster files are stored in databases?

When storing raster files in PostgreSQL only one row is created in the database, but the raster is a matrix so typically it must create rows and columns. I want to understand how raster files are ...
0
votes
0answers
54 views

Error's while retrieving data from postgres to Qgis using python?

This is my code of python in qgis from qgis documentation docs uri = QgsDataSourceURI() uri.setConnection("localhost","5432","gis_data","postgres","vajja") ...
0
votes
1answer
104 views

With schema, function postgis_full_version() does not exist

A rails application is configured to run with postgis. Database.yml is configured as follows: development: adapter: postgis database: app_development username: simpleton schema_search_path: ...
1
vote
0answers
96 views

Issues creating ArcSDE sde_logfile for certain users (username begins with a numeral)

I am having issues when editing an sde feature class from certain users. The way my database is set up is that the authentication roles are updating constantly by comparing the current authentication ...
1
vote
1answer
38 views

Get points in a union of circles

I am trying to create a postgis query that would get all the points in an union of circles (possibly more than a thousand of circles with different radius). The circles would be created dynamically ...
0
votes
1answer
65 views

Error while connecting qgis with postgres?

sql="select long,lat from poidata.poi_ca from where city LIKE '%unnyvale'" uri = QgsDataSourceURI() uri.setConnection("localhost","5432","gis_data","postgres","vajja") ...
0
votes
1answer
162 views

Issue using pgr_dijkstra from Postgres pgRouting

I am using Postgres with Postgis extension to retrieve streets (only for pedestrian use) and several "natural" points. I need to make updates every minute and i am working on a small bounding box. For ...
2
votes
2answers
283 views

Problem with PostgreSQL View layer in QGIS

I have a PostgreSQL table and view: CREATE TABLE lines ( geom geometry(MultiLineString,4326), type character varying(254), login text, gid serial NOT NULL, ...
1
vote
0answers
52 views

Postgis ST_Azimuth only returning a small range of angles

I have a puzzler with postgis and ST_Azimuth. I have a table of points (taken from country borders, and run through the QChainage plugin). I want to find the azimuth from a fixed point (15.0,50.0) to ...
1
vote
0answers
75 views

using changeset for larger region to update OSM data in PostGIS

I'm using the OSM import and update workflow described here to maintain OSM data in a PostGIS database. Import (osm2pgsql): osm2pgsql -c -d gps -U gps --cache 8000 --number-processes 4 --slim ...
1
vote
1answer
107 views

How to select from geometry column in postgresql?

I've got a table which it has a geometry column filed by some point (ex. "POINT Z (116.309627 39.991285 148)"). now I want to select point's id from the table. I wrote follow sql Code but I force an ...
1
vote
1answer
48 views

Intersection of two vector tables data in postgresql

I have satellite raster data that is converted into pixels using CREATE TABLE gtru_tile_pix AS SELECT (ST_PixelAsPolygons(rast)).val, (ST_PixelAsPolygons(rast)).geom FROM gtru_tile_raster; ...
1
vote
1answer
82 views

A few PgRouting database design questions

Does someone know how the cost field in PgRouting is calculated? What are 'source' and 'target' good for? 'osm_source_id' and 'osm_target_id' contain the same information using different numbers. Is ...
1
vote
1answer
61 views

Percent total from postgis clip

How would I calculate the percentage of streets that have sidewalks summarized by census tracts using PostGIS. My sidewalks file has an id that links it to its adjacent street and I've worked out how ...
1
vote
0answers
64 views

GeoTools How to add attribute to a schema in DataStore

As you know, you can create a schema in a DataStore. For example, you may create a table in a postgresql database like this: SimpleFeatureType schema = ...;// some schema DataStore dataStore = ...
0
votes
1answer
49 views

pgsql2shp error: table does not exist

I am exporting tables from Postgresql 9.2 to shapefiles using postgis 2.0. I have the same system on other machines and it works correctly. I have tried running the command from a prompt and get an ...
3
votes
2answers
180 views

How to classify a raster using QGIS Plugin interacting with PostGIS?

I'm developing a QGIS plugin where users can enter some parameters (like distance, depth, area...) and by submiting the plugin interact with a PostGIS database and extract informations form vector and ...
0
votes
1answer
70 views

Average distances between two point sets bound by polygon

I have a table of boundary polygons, a table of points and a table of other_points I want to work out the average distance between points contained within and other_points for each polygon. So far I ...
1
vote
0answers
65 views

postgres update and cluster not empty

I want to update my postgres/postgis server (on Windows) moving from version 9.2 to 9.3, and 2.0 to 2.1 for postgis. I am using pg_upgrade bu stuck on a error : New cluster database "postgres" is ...
1
vote
1answer
110 views

Couldn't Excute command line PostGIS pgsql2shp

I am using postgres 9.3 with postgis 2.1 on Ubuntu 14. When I tried to dump pgsql table using pgsql2shp, I get the following error. Error: pg_wrapper: pgsql2shp was not found in ...
1
vote
2answers
158 views

Rasters loaded in Postgres/PostGIS using raster2pgsql contain blank lines

I recently set up an Ubuntu Server as a Postgres/PostGIS database server. In an effort to serve a collection of aerial photos to my colleagues, I loaded 608 GeoTiffs (non-overlapping 1km tiles each ...
0
votes
1answer
247 views

Nominatim installation error: Out of memory for node cache dense index

I am installing Nominatim on my Ubuntu server 14.04 through this link and trying to load OSM data into my postgreSQL database. For loading into my database I am using this command as given in the ...
4
votes
1answer
92 views

Slow Planning for Postgis ST_Intersects

I am trying to run the following query: SELECT * FROM fences where ST_Intersects(geom, ST_GeomFromText('POINT(-73.990452 40.994184)', 4326)) When I run Explain Analyze I get the following: "Index ...
2
votes
0answers
79 views

How to create MBtiles from geoserver [closed]

How to generate mbtiles in geoserver? using openlayers for displaying geoserver layer for example calling wms layer like this new OpenLayers.Layer.WMS("Kanpur", ...
1
vote
1answer
54 views

How to use the Tile Server to fetch latitude/longitude data

I have installed a tile server using the following link and my main purpose was to geocode as many times as possible. Initially I was using openstreet API to geocode but they have certain limits so ...
2
votes
1answer
122 views

Convert Postgresql Polygon to PostGIS POLYGON

I have an existing table in postgres which contains a plain old posgresql polygon. I have added a new column using SELECT AddGeometryColumn ('public','fences','geom',4326,'POLYGON',2); I would ...
0
votes
1answer
286 views

Osm2pgsql failed due to error when I try to import data into PostgreSQL

I am using the following link to install openstreetmap server on my ubuntu 14.04 server. I have followed their tutorial line by line and installed most of the dependencies and everything. When I try ...
1
vote
1answer
131 views

Performance Improvement of Postgres vs PostGIS

I am trying to run the following query thousands of times per second to determine which is any polygon contains the passed in point. SELECT * from areas WHERE poly @> ? Poly is the name of the ...
0
votes
0answers
31 views

GeoWebCache Speed SQL Server Store

How to increase GeoWebCache speed, Am using geoserver 2.7.2 for tile cache and data store creating from a SQL Server database it will take 1 day and above to complete cache and it terminated several ...
1
vote
1answer
65 views

How to store multi-valued attributes in PostGis using Postgresql [closed]

I have a with meterid,meterreading,reader and date read.I wish to store this data using Postgis database. I used this code via postgresql to create the table CREATE TABLE MeterRdg( meterid ...
0
votes
0answers
21 views

how to use st_intersection() with re-projection in POSTGIS?

This is a shipping route from 'portland to busan' and I want to check the route against land_polygons like how much distance is traveled by a route through land.And the problem is while checking the ...
0
votes
1answer
67 views

Alpha Shapes in pgRouting - missing FROM-clause

I am attempting to follow Anita Grazer's post on creating alpha shapes in pgRouting. The article was written several years ago, so some of the functions have been updated and names have changed. I ...
0
votes
0answers
54 views

Aggregate/merge data upon insert into PostGIS (via gdal)

I am working on a project where I take a large raster file (1.5GB+), put it through a number of image-processing operations, and finally convert it to vector data. Being that the raster is large, I am ...
3
votes
3answers
649 views

PostgreSQL trouble editing points / lines / polygons in QGIS

I have a problem with postgresql and QGIS : I can add points/lines/polygons and fillup field But I can't : delete points/lines/polygons modify points/lines/polygons (move or modify field) ex : ...
2
votes
1answer
97 views

How to set/update columns by using a trigger?

I want to set/update attributes (alignment_horizontal, alignment_vertical) of a PostGIS geometry (point) by using a trigger. CREATE TABLE point ( gid serial NOT NULL, geom geometry(point, SRID), ...
0
votes
0answers
43 views

To find a substring of a linestring in postgis to be used in playback in map

I have gps coordinates as point outside the linestring/road. I have interpolated to the closest point in the line using ST_Line_Interpolate_Point(). Now I need to draw line through these points and it ...
3
votes
1answer
120 views

What tools do you recommend to draw historical shipping routes?

I´m working on a project where I have to draw historical shipping routes of trade, whaling, etc. ships from the XIX century. I´ve done some research and checked some questions in this forum but I ...
2
votes
0answers
28 views

Creat point count trigger in PostgreSQL?

I wish to create a trigger in PostgreSQL for counting points in a polygon. The tweak is that the count should automatically get updated whenever the polygon is edited. Can anyone please help me on ...
0
votes
0answers
35 views

Cant able to delete features of a table inside a public schema of postgre database

I have table inside a public schema of postgre database. And i did a connection to that database and load the table inside the public schema in QGIS 2.10.1. I changed the table to edit mode and tried ...
3
votes
2answers
75 views

Is the PostGIS st_intersection function viable for very large data-sets?

I am running an area intersection query on two large multipolygon tables (a few million rows). SELECT r.geom FROM public.my_table r WHERE EXISTS ( SELECT gid FROM public.my_table_intersection ...
3
votes
2answers
849 views

Cleaning geometries in PostGIS?

I am trying to do some processing on some very large polygon layers. However I am running into various geometry errors such as: NOTICE: Ring Self-intersection at or near point 470396.52017068537 ...
0
votes
1answer
78 views

Import shapfile to Postgis using Python and ogr?

Iam not able to import a shapfile to PostGIS using the same library(ogr). I am using below link but it give me error. Import shp to Postgis using Python and ogr Error : Traceback (most recent call ...
1
vote
0answers
143 views

Nominatim CentOS 7 Install Problem

The Problem: I've installed Nominatim on CentOS 7 because I want to do some reverse geo-coding, however, when I try to load the and create a Nominatim database with this command: ./utils/setup.php ...
1
vote
1answer
309 views

driving time to the nearest facility using pgrouting

I'm using pgrouting 2.0 and postgis in postgresql. My problem is to calculate the driving time to the closest facility for each node of my OSM derived network. These are the steps to be implemented ...
0
votes
1answer
69 views

Problems with PostGIS St_closestpoint function

I'm trying to find the closest point in a line from an insertion point closest to it. To do it, in the middle of the function I use select id from rede_viaria.t_rviaria_via vias where ...
2
votes
1answer
88 views

How can I do a subquery inside a subquery in PostGIS?

I need to add a subquery to a working function. Instead of select * from table, I'd like to select * from (select * from table where height>1500) as sub, something that works in other functions I ...
1
vote
1answer
62 views

Is Create Spatial Type necessary

When I enable an enterprise geodatabase for a database in postgresql, the st_geometry type get installed with it. So Is it necessary to use "Create Spatial Type" tool to create st_geometry type in ...