PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

2
votes
2answers
250 views

Update/refresh edited PostGIS table in QGIS

I imported a PostGIS table (with geometry) from my PostgreSQL database as a layer in QGIS. Later, I edited the table using PostgreSQL UPDATE commands. Is there a way to quickly update (like a refresh ...
1
vote
1answer
55 views

How do I sum values that are geographically close to each other?

I have a dataset of Boko Harams rampage in Nigeria uploaded to CartoDB. I have columns like coordinates, dates, number of victims. Would it be possible to write a query that would sum the victims ...
1
vote
1answer
41 views

PostGIS returning a field one level higher in the Json

I have the following SQL on a PostGIS Database ($properties contains a list of fields, including osm_id) SELECT row_to_json(fc) FROM ( SELECT 'FeatureCollection' As type, ...
0
votes
0answers
29 views

QGIS Crash: Enter Credentials

just wondering if anyone has come up against this issue...in the office QGIS will come up with an 'Enter Credentials' box with 'Error: Too many clients already'. When you enter your username and ...
4
votes
4answers
823 views

What does the “service” box in the PostGIS connection dialog stand for?

Can sоmеbody explain me what to write in the "service" box when connecting to the local DB. QGIS Lisboa and Postgres are installed.
1
vote
0answers
45 views

SQL query works in PostgreSQL but not in GeoServer SQL View

I can't find a solution for this problem after a couple of hours of research. The query is: SELECT searchaddress('some string'); FETCH ALL IN mycursor; The function searchaddress returns a ...
1
vote
1answer
54 views

how to query contour postgres db for elevation at a point using postgis

Is there a way to interpolate between these contour lines for a given point using postgis? Thanks. I've come up with the following query to gather the potential contour lines of interest: ...
2
votes
1answer
76 views

ST_INTERSECT of each element of two tables (polygons, lines)

Im new to postgis and trying to solve the following problem: I have two tables: 1. containing polygons 2. containing lines I need to find out the id of all polygons on each line and fill them in new ...
0
votes
1answer
369 views

PHP/MySQL, PostgreSQL spatial queries. Find a polygon that contains a coordinate? [closed]

I am building a non mapping javascript app and I am wanting to take a lat/long coordinate and query a MySQL table of polygons for the one that contains the point. I have looked into MySQL spatial ...
2
votes
4answers
1k views

Postgis shapefile loader 2.1 error: dbf file can not be opened

The .shp, .dbf and the rest of files are fine, I open them in QGIS, AGOL with no problem. In fact, I've tried 3 different layers, and get always the same error. Anyone can please help, as I really ...
2
votes
2answers
278 views

Using ST_split to split a line feature using nearby points

I have a line shapefile and a point shapefile, and the line has just one segment and its attribute table, the point shapefile has points near the line above, not on it and the close ...
0
votes
0answers
33 views

Routes through centroids and array functions

I have a table stopcentroids4 like in figure with the following fields: gid, routes, geom and i want to draw a line for each route that runs through centroids. In my code there is a mistake. ...
1
vote
1answer
141 views

Force St_split() to return geometries in specific pattern

I'm Using PostGIS st_split() to split a polygon geometry by a linestring. Then I use st_dump() to return each of the splitted geometry and it is working fine. But the issue is I want to force ...
0
votes
3answers
438 views

Unable to connect to PostGIS databases after Mac OS X Yosemite upgrade

I upgraded to Mac OS 10.10 Yosemite and am unable to connect to PostGIS databases (PostgreSQL version 9.3 and PostGIS installed from KyngChaos). Typing psql my_database returns this message: psql: ...
1
vote
1answer
62 views

How to create postgresql triggers to calculate geometry from x,y and vice versa at the same time

I have a postgis table with point geometry which looks like this CREATE TABLE mypoints ( gid serial NOT NULL, coordid integer, geom geometry(Point,4326) ); And a table with coordinates ...
1
vote
2answers
37 views

How can I add extensions to PostGIS?

As newbie with PostGIS, I downloaded Postgresapp for macosx. Everything works fine, but I noticed some differences with Stackbuilder which adds to extensions address_normalizer and grouting. Can I add ...
4
votes
3answers
2k views

group by timestamp interval 10 minutes postgresql

I need some help on this: I need to group by a period of time spliting up by 10 minutes: I have this query below that returns me a result set SELECT timestamp FROM table_1, ...
0
votes
0answers
24 views

ST_Touches Function in Iterative Process

Im a complete newbie to postgis. I have the following problem: Got two tables: "mp_power_lines_new" -> contains lines of an electricity grid "uw.new"-> contains polygons of all substations in this ...
1
vote
2answers
30 views

Learning pg_routing - shortest_path error

I'm learning PostGIS and pg_routing to help in our project planning and grant applications at my transportation agency (we wanted to move beyond the limitations of the 1/4 mile walking buffer we use ...
0
votes
1answer
57 views

ST_AsGeoJSON function slow in PostGIS

I'm currently trying to pull neighborhood data from Zillow's Neighborhood page (http://www.zillow.com/howto/api/neighborhood-boundaries.htm) and then load it onto a leaflet map, openstreetmap. I ...
1
vote
0answers
27 views

how Npgsql.EntityFramework support Spatial data

I am using Npgsql.EntityFramework + EntityFramework6.My database is postgresql. I Added to my C# project with this tutorial : ...
4
votes
1answer
186 views

PostGIS doesn't use index for spatial query

I can't get PostGIS 2.1 running on PostgreSQL 9.3.5 to use a spatial index even for the simplest queries. The whole dataset is 8 million points (population count grid from here ...
1
vote
0answers
52 views

how to automatically update column based on the second layer, trigger, postgreSQL

I am newbie in postgreSQL. I have 2 tables, observation and landcover, i want to create a trigger which would allowed to update the column observation.land based on the spatial information according ...
0
votes
1answer
35 views

geometries with different SRIDs errors (even after setting SRID)

I've looked at existing questions about this (including this one) and followed the advice that was shared there. I'm still seeing this when I run some queries: RuntimeError: Postgis Plugin: PSQL ...
0
votes
0answers
70 views

box2d spatial type

I'm storing data that defines a box. I created a column by adding it to my table as follows: SELECT addgeometrycolumn('grid', 'grid_geometry', 32650, 'GEOMETRY', 2); I then insert my data in using ...
1
vote
1answer
37 views

Basic sp-gist indexing?

Please forgive a very, very elementary question. I am using a PostgreSQL 9.3.5 database, and one of the tables stores longitude and latitude. (These are two separate columns.) I would like to do ...
1
vote
1answer
94 views

PostGIS - Intersects feature to split into linestring

I would like to know if it's possible to intersect feature datasets (multipolygon, polygon, multilinestring and linestring) to get a result as a set of linestrings being split at each intersection ...
4
votes
1answer
3k views

Why is PostGIS query not using spatial index?

I am having an issue with a spatial query that is not making use of indexes. My schema is: CREATE TABLE geoplanet_place ( woeid integer NOT NULL, "name" character varying(300) NOT NULL, ...
2
votes
1answer
47 views

PostGIS replace pixel value by lookup table?

I was wondering if it is possible to write a PostgreSQL statement to replace the value of each pixel in an input PostGIS-raster with a value obtained from a look up table. For example, suppose I have ...
0
votes
1answer
315 views

How to get points in bounding box by degrees for map

I have some concerns about working of BOX3D PostGIS function. I tried to find places in square -158.721365889063 66.09275164148659, 154.608712235937 52.56807548751039(degrees). But this where ...
4
votes
1answer
49 views

Finding all segments that makeup a road in OSM

I've spent 2 days trying to figure this out and I'm afraid that it's something that's not possible. If that's the case then I'm thinking the wrong with with respect to OSM data. Let's say I have a ...
1
vote
2answers
531 views

Function for Selecting Points in a Polygon

I've been banging on this for a few hours, but since I'm relatively new to PostgreSQL and PostGIS, I can't find the solution. I'm trying to create a stored procedure (function) which will return all ...
2
votes
0answers
62 views

How do you disconnect ArcGIS Pro from a PostgreSQL database?

What is the equivalent to the ArcCatalog's Disconnect option in ArcGIS Pro? I have successfully connected to a Postgresql database, but cannot figure out how to disconnect. This is what I'm seeing ...
1
vote
0answers
63 views

Create GeoServer SQL View with variable number of arguments ( double precison [ ] )

I was trying to create a PostGIS procedural function which will perform a particular operation depending upon the user's provided coordinates. But since the user can provide any number of coordinates, ...
3
votes
2answers
74 views

pgrouting - network distance many-to-one shortest paths

I want to find the distance (km) between each origin point to the closest destination point (restaurants) using pgrouting. I have 3 tables my road network with nodes and edges 140 origin points - ...
2
votes
2answers
99 views

How to change from EPSG:900913 to EPSG:3857 in Postgresql

I didn't use the '-E 3857' switch when importing the OSM data into postgresql using osm2psql. I'm trying to set up a map server for the first time and I'm following the instructions from here: ...
6
votes
2answers
421 views

How to check if points are within a circular sector using PostGIS?

I want to get the stars within the black region as shown above. To find this I have the centre of circle (lat, long), radius and the angle of region (45 degree). I am new to postgres. I worked on ...
2
votes
0answers
37 views

filling the attributes according to intersection of two layers (Postgresql)

How to automatically fill attributes of a point layer according to the exact position in the polygon layer? I mean how to write a command in PostgreSQL to automatically fill the attributes by the ...
32
votes
6answers
10k views

How to use PostgreSQL/PostGIS with ArcGIS for Desktop? [duplicate]

Has anyone used PostgreSQL with PostGIS successfully? I am a semi-experienced ArcGIS user and want to build out a database and connect it to ArcCatalog. I've scoured the internet for resources but ...
2
votes
0answers
18 views

What are the problems of loading overlapping (or the same) tifs into postgis?

I am still early on the Postgis learning curve. I have an existing table in Postgres - it looks like this: create table "public"."mytable" ( "rid" serial primary key, "rast" raster, "filename" ...
2
votes
3answers
1k views

What's the fastest way to do a bounding box query in PostGIS

I have a Postgres table, with almost 2 million rows, with a long-lat coordinates field in the form POINT(-73.4938 33.2405). Supposing there's a geospatial index on that field, what's the most ...
4
votes
2answers
213 views

Why is the Google Maps circle shape not matching to the distance calculated with PostgreSQL?

I am running a PostgreSQL 9.1 Database with PostGIS 1.5 extension. Several coordinates are stored in there with type GEOMETRY (SRID 4326). I'm building a web-application with Google Maps Javascript ...
0
votes
0answers
54 views

How do I break up a shape file into contiguous area?

I've got a shapefile which is a union of non-contiguous shapes. I'd like to be able to break them up into individual groups. For example, suppose I have a polygon of the entire US, currently only ...
1
vote
1answer
60 views

How to select geography features within a certain distance given in miles?

I am just taking the first steps in using POSTGIS, but having trouble finding a simple example. I have a database with a Key field, lat and lon fields and a GEOGRAPHY field. I just want to have a ...
8
votes
1answer
190 views

What spatially-aware GUI administration softwares exist for PostgreSQL/PostGIS (free or paid)?

Most of my experience with PostgreSQL has been PGAdminIII and psql, but I'm interested to know what others use. Is there PostgreSQL equivalent to SQL Server Management Studio (SSMS) and its ability ...
0
votes
1answer
99 views

Creating plugin in QGIS to access PostgreSQL? [closed]

i want to develop a plugin in QGIS to access to my database in postgres : i want to import a file.sql that contains my SQL Code in a textbox , and execute this code to assign myd database.
1
vote
1answer
239 views

QGIS cannot view raster loaded to PostGIS through raster2pgsql

I tried to load a raster GeoTIFF image to a PostGIS database using "raster2pgsql" following the OpenGeo Suite Manual. The command I used is: C:\>raster2pgsql -s 4283 -I -C -M "C:\Program ...
4
votes
3answers
312 views

GIN/GiST Full Text searches through openlayers, geoserver and postgres

I have a a web app that automatically maps recent tweets and allows you to search for certain keywords. it uses Postgres for the database, geoserver as the server, and openlayers as the cartographic ...
1
vote
1answer
1k views

Geoserver sql view using an external sql query?

I am trying to figure out a way of producing a view of a layer in GeoServer, using an external sql query. By external I mean, for instance, a user defined query in a php from where all the parameters ...
2
votes
1answer
58 views

Is it possible to use spatial function to retrieve non-spatial data?

I'm using PostGIS in postgresql.I wish to calculate points within the radius from particular geocode.For that i have created a table with spatial column geocode having datatype as geometry and ...