Questions tagged [postgis]

PostGIS is a database extension for PostgreSQL providing additional spatial types, indexes, functions, and aggregates.

Filter by
Sorted by
Tagged with
15
votes
3answers
8k views

PostgreSQL vs. MySQL: spatial feature comparison

We are the in the process of building out a web application that has a spatial data component. In the beginning our spatial data comparisons will take a given point and return matched overlapping ...
7
votes
1answer
4k views

Order by distance

If I have a query returning nearby cafes: SELECT * FROM cafes c WHERE ( ST_DWithin( ST_GeographyFromText( 'SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')' ), ...
2
votes
1answer
10k views

Select first row (grouping) + add aggregate function

First have a look at this question on StackOverflow. I'm looking to accomplish the same task, except I also need to add an aggregate function (PostGIS's ST_Union) to my query. How can I combine the ...
3
votes
1answer
2k views

Finding the nearest geo points across two tables?

I have two tables, both have lat/lon columns within same country (UK). Tables sizes are roughly 80M and 50M. Along with the lat/lon columns I have created a geo-indexes for both tables in this way: ...
2
votes
3answers
566 views

GIS Point or discrete longitude and latitude columns?

I see that the Point type is made to store geometric coordinates, however I don't see what problem it is trying to solve, or what advantages it offers over simply using two numerical columns. It's 16 ...
0
votes
1answer
420 views

How do I go from a PostgreSQL native point type to a PostGIS point?

If I have a point SELECT point(long,lat); How do I go from that to a PostGIS point?
12
votes
2answers
39k views

PostgreSQL: Cannot change directory to /root

I am trying to copy a table planet_osm_polygon from one database osm to another test. I su postgres and performed the pg_dump. Problem: However I'm getting the error could not change directory to "/...
3
votes
1answer
1k views

&& vs ST_Intersects performance

I am trying to figure out points reside inside an rectangular area (envelope). I am having a little bit difficulty understanding performance implications of using && operator compared to ...
9
votes
2answers
4k views

Order of columns in a compound index in PostgreSQL (and query order)

I have a table with 50K rows. It is actually a PostGIS table. The query has 4 parts (1 mandatory) (3 Optional) intersection box (a geography rectangle) with 4 lat,long (I use st_intersects) [...
3
votes
1answer
511 views

Very large btree index with few rows (openstreetmap gis data)

I tried to import a subset of open street map data to a Postgres database using the standard tool (osm2pgsql). I then tried to rebuild the indices on the table using plain sql (dropping them, then ...
2
votes
2answers
107 views

Extract data along a path (lat, long, altitude and time)

I will be deploying PostGIS in the next few day. I've never done this before, nor have I ever worked with and geo-spacial data in the past. I will have two datasets: weather data (A) and coordinate ...
2
votes
1answer
215 views

PostgreSQL Geo Spatial Query is slow

After given up with MySQL i have tried Elasticsearch and now wan't to see if I could go with PostgreSQL/PostGIS that could allow me to go with PostgreSQL only. I need to fetch records from a Table ...
2
votes
2answers
1k views

Adding standard set of constraints (rules) to PostGIS `raster` type column

Following on from my related prior question, let me elaborate this topic a bit. raster2pgsql is a raster loader executable that loads GDAL supported raster formats in PostGIS. It has a -C flag ...
2
votes
2answers
972 views

Creating a unique constraint on a PostGIS 'raster' type column

I am using the following command to add constraints to one of the raster image in PostGIS-2.1.3 (PostgreSQL-9.1.14). ALTER TABLE schema1.table1 ADD CONSTRAINT enforce_scalex_rast unique (rast); But ...
2
votes
2answers
683 views

UPDATE table with another table data

I want to update the table car_check field fencing.(total 40 rows).I also use postgis function. I think it's a little difficult.Any suggestions? The code is the first carid example. UPDATE car_check ...
0
votes
1answer
107 views

What is the easiest way to install the latest postgis_tiger_geocoder?

Ubuntu distributes PostGIS with a core extension called PostGIS TIGER Geocoder, packaged as postgis_tiger_geocoder. Currently, as of PostGIS 2.2.2, it pulls data from TIGER2015. I would like to ...
0
votes
2answers
360 views

How can I manage an exception generated by Postgis?

I am running the following query on Postgres 10.3/Postgis 2.4: create table table_c as ( select A.*, B.area_name, B.id from table_a A left join table_b B on ST_Contains(A.geom, B.geom)...
0
votes
1answer
245 views

Can PostGIS use spatial indexes on foreign tables?

I'm attempting to perform a query on some foreign spatial tables which is taking several orders of magnitude longer than running on the normal spatial table because the index is not used. Indexes ...
0
votes
1answer
153 views

PostGIS geocoding from stdaddr?

PostGIS provides many different methods of address standardization Some newer methods return stdaddr, standardize_address (which requires explicit lex, gaz, and rules) Others return norm_addy ...