PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

2
votes
1answer
638 views

Create isochrone from multiple start points

I want to create an isochrone. For this I could use the pgr_drivingDistance function with pgRouting (http://docs.pgrouting.org/dev/src/driving_distance/doc/index.html) but it only takes one start ...
3
votes
2answers
54 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 ...
0
votes
0answers
32 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 ...
1
vote
0answers
35 views

Running a custom function on each line of a PostGIS table

I am trying to run a custom function on each parcel in a PostGIS table, (in this case its a function that finds the standard deviation value of the pixels intersecting a raster) Here is the code for ...
0
votes
1answer
31 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
39 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 ...
0
votes
1answer
35 views

Problem importing sqlite into Postgres (with PostGis) using ogr2ogr

I am trying to import a very large sqlite file into Postgres SQL 9.3. The first tool I tried to use was OGR2GUI (http://www.ogr2gui.ca/). The 64 Bit version does nothing, whilst the 32 bit version ...
1
vote
1answer
43 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 ...
0
votes
1answer
147 views

Google Streetview Images in infowindow

I have followed the instructions on this blog post on adding streetview images to the infowindow: http://blog.cartodb.com/a-picture-is-worth-1-000-attributes-infowindows-with/ I can do it without a ...
1
vote
2answers
220 views

adding geometries to a source, target table for multiple rows in pgrouting

I am having an issue trying to perform the pgr_dijkstra algorithm and getting back a line in my table for multiple rows. I have a table that has a columns of source, target node id's but I cannot ...
3
votes
1answer
42 views

GeoServer and PostgreSQL JSON column

I have a database table with a dynamic schema implemented via a JSON column named "properties". Currently GeoServer doesn't recognize my JSON column. Is there some way to make Geoserver recognize ...
0
votes
1answer
40 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 ...
0
votes
1answer
238 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 ...
2
votes
1answer
51 views

How to detect if point is on the left or right side of the line in PostGIS

I have a linestring table and a point table in postgis. I know the closest line to any given point. What I need to know is on which "side" of that line is the point. I guess I have to do that by ...
8
votes
3answers
838 views

What are the Pros and Cons of hstore data type in postgis?

PostgreSQL has a module named hstore that implement the use of key/value pairs (it's something like the OpenStreetMap model, but in a single table). What are the Pros and Cons of hstore regarding GIS ...
1
vote
0answers
28 views

PostgreSQL default values in QGIS 2.8.2

I think the issue outlined in this question PostgreSQL default values in QGIS 2.8.1 and ticket #12391 is not fully solved yet. Negative numeric default values, and dates are not used by QGIS 2.8.2 ...
9
votes
1answer
207 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 ...
3
votes
1answer
132 views

How to create a trigger for multiple schemas?

One of my PostgreSQL databases contains different schemas which share the same structure. -- schema region_a CREATE TABLE region_a.point ( gid serial NOT NULL, geom geometry(point, SRID), ...
2
votes
1answer
55 views

How to find Shortest Route between two points in PostGIS without using pgRouting?

I want to find shortest route between two points stored in PostGIS database. I know about pgRouting functionalities but I don't want to use it because of data processing/topology creation processes. ...
2
votes
0answers
41 views

Calculate mean value of polygon from raster in PostGIS

I started with a NetCDF .gri and .grd raster file of the UK provided by a colleague. I clipped it in R to be only London, exported and converted to a ASC file, and then imported it to PostGIS using ...
2
votes
1answer
93 views

Trouble using ST_Difference to remove overlapping features

I am trying to use ST_Difference to create a set of polygons (processing.trimmedparcelsnew) that do not contain any of the area covered by another set of polygons (test.single_geometry_1) using ...
6
votes
2answers
7k views

How to connect to Postgres with QGIS?

Oddly, it should be simple, but I can't connect to a local PostGIS database. My installation is with Mac OS X (10.8), Postgres.app (in doc it's said to have PostGIS included) and Quantum GIS 1.8. ...
1
vote
0answers
285 views

SQL-query should return GeoJSON FeatureCollection

I'm trying to return a GeoJSON FeatureCollection object from a SQL-query with PostgreSQL 9.3 and PostGIS 2.1. This is my query so far: SELECT row_to_json(fc) AS p FROM (SELECT 'FeatureCollection' ...
0
votes
1answer
28 views

PostGIS: Invalid Input Syntax for Type Double Precision

I have this query that is trying to update my geom column that's built off of lat and lng coordinates UPDATE main_table SET geom = ST_SetSRID(ST_MakePoint(lng, lat) ,4326); However it returns that ...
2
votes
0answers
29 views

match noisy GPS data to closest linestring in road network [duplicate]

I have some noisy GPS data(Points) that they are not exactly on the road because of the noise. But I want to match them to the closest road linestrings. How to do this in PostgreSQL? Data: ...
0
votes
1answer
35 views

I'm not able to add SQL file to postgresql database

I started with basics and did these steps: createdb -U postgres -E UTF8 -O Peete gis -OK createlang -U postgres plpgsql gis (was already installed) -OK psql -U postgres -d gis -f ...
3
votes
2answers
2k views

PostGIS Error: type “geography” does not exist

I just upgraded my development machine to Windows 7 and am therefore reinstalling PostgreSQL and PostGIS. I used the Postgres Plus installer, as recommended on the Postgres website, and followed this ...
0
votes
1answer
21 views

CartoDB JQUERY function init() fail

Absolutely stumped. Simply attempting to toggle layers using cartdb and jquery much like this tutorial. After sniping the console I get init() is not defined? However this only occurs after adding ...
0
votes
0answers
19 views

How to create a polyline using multiple points using a PostgreSQL query?

I'm trying to use point data to create a polyline to show my bus route. Initially began with coordinates and used this to create geom using the ST_MakePoint function (written to the BusRouteData ...
2
votes
2answers
304 views

How to calculate the track length of GPX files with PostGIS?

I am trying to calculate the length of a GPX track. The original GPS data looks like this: <?xml version='1.0' encoding='UTF-8' standalone='yes' ?> <gpx ...
1
vote
0answers
25 views

Unable to load Postgresql spatial data in R (with rgdal)

I'm trying to load spatial data from a postgresql database in R, using functions from the package rgdal, but I'm having trouble getting the functions to work. For example, when trying to read a ...
1
vote
1answer
31 views

PostGIS nearest point with LATERAL JOIN in PostgreSQL 9.3+

I need to obtain on each element on one table the closest point of another table. The first table contains traffic signs and the second one the Entrance Halls of the town. The thing is that I can't ...
0
votes
0answers
5 views

postgres 9.2 update query on a subset

I wish to make an update using CASE, on a subset of a table. This subset is based on a spatial query. It looks like : update t1 set f1 = CASE WHEN... WHEN... END FROM (select * from ...
0
votes
2answers
25 views

different calculation results

I spend a comparison of functions of spatial analysis and Oraсle postgresql. There are two tables with the same coordinate system - tech_bridge. oracle query select ...
1
vote
2answers
56 views

Adding additional geometry column in PostGIS

I'm importing many sets of geodata into PostGIS, and they have different SRID's. (Some have EPSG:3857, some EPSG:4326, some something else). I'd like to create an additional geometry column, eg. ...
10
votes
2answers
2k views

GUI tools for PostgreSQL/PostGIS?

as a cartographer I work with TileMill to visualize geodata. I installed a local PostgreSQL server with PostGIS and osm2pgsql. Everything works fine. Anyhow, I am new to working with servers and the ...
1
vote
1answer
29 views

pgrouting: function driving_distance does not exist

I tried to run driving distance function but got error. function driving_distance(unknown, integer, integer, boolean, boolean) does not exist LINE 1: SELECT * FROM driving_distance(' ...
2
votes
2answers
3k views

PostGIS convert geography(Point,4326) to GPS

I got a database with a column of type geography(Point,4326), the data contained looks like 0101000020E610000092E057F66EF73140930035B56CBD4840. How can I convert it to GPS coordinates?
0
votes
0answers
30 views

Enable PostGIS extension on Ubuntu 14.04.2

I have troubles enabling PostGIS extension on Ubuntu 14.04.2 and PostgreSQL 9.3. I have installed the packages with this step by step procedure. PostgreSQL and pgAdminIII seem to be installed ...
6
votes
0answers
75 views

Best way to cut linestrings with points

i was checking the best way to cut linestrings by points. The scenario is: lots of streets, need the segments cut by intersect points, kind of this: I got linestrings (full uncuted by points) ...
0
votes
2answers
571 views

Select points from a table that exist in a custom polygon

We have a property search site where the user draws a polygon on a map and then the app displays all properties that fall within the polygon. We have properties stored within a Postgres table that ...
1
vote
1answer
55 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
1answer
20 views

Tweaking the table after shapefile import?

I'm importing shapefiles to Postgresql with the shp2pgsql command line tool. shp2pgsql -s 3035 -W "latin1" foo.shp | psql Should I do some table (SQL) tweaks after that? Or is the import fine after ...
0
votes
0answers
34 views

Cannot find information about my.table table in raster_columns

I am running PostgreSQL 9.4 and PostGIS 2.1 and have loaded a small piece USGS SRTM data for North America successfully into my database. I am attempting to export a small piece of of the rasters I ...
4
votes
0answers
52 views

How to speed up simultaneous working with PostGIS layers in QGIS?

we are working on one big layer at the same time (10-20 people). Some of us use QGIS 2.8.1 and the other 2.6. The user with 2.8 have a lot of crashes (QGIS). The 2.6 users don't have this crashes but ...
0
votes
0answers
56 views

Can't upload files to database by Nominatim

I was trying upload geofabric's netherlands-latest file into Nominatim's database. I used bellow command and all was fine: ./utils/setup.php --osm-file maps/netherlands-latest.osm.pbf --all 2>&1 | ...
1
vote
0answers
39 views

How to install PostGIS shp2pgsql-gui on openSUSE 13.2?

I installed PostgreSQL 9.4.4 together with pgAdminIII (1.20.00) and the PostGIS extension (2.1.7, via the Stack Builder) from http://www.enterprisedb.com/products-services-training/pgdownload on my ...
0
votes
0answers
44 views

PostGIS ST_Intersects not returning values

I recently installed the PostGIS extension for a rails app that i'm working on. Currently, i am trying to get all the polygons that intersect the vieport of the Google Maps. On running the (see ...
0
votes
0answers
45 views

Installing odbc_fdw in Postgresql 9.3.5 in Windows

I need to install odbc_fdw on my postgresql 9.3.5 which is running on Windows server 2012. So far, I've found this version which only works for postgres 9.1. I found this other version that should ...
0
votes
0answers
83 views

Problem with ST_IsValid: Unknown geometry type: 9 - CompoundCurve

The following is an example of a gml:Curve that was loaded into Postgres using FME. <gml:Curve srsName="urn:ogc:def:crs:EPSG::27700" gml:id="os.vmd.ny.38124.geom"> <gml:segments> ...