Skip to main content

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
1 answer
9k 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 || ')' ), ...
Gandalf StormCrow's user avatar
3 votes
1 answer
11k 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 ...
Jordan Arsenault's user avatar
17 votes
3 answers
14k 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 ...
Ryan Charmley's user avatar
4 votes
1 answer
4k 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: ...
Randomize's user avatar
  • 1,183
2 votes
3 answers
813 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 ...
davidtgq's user avatar
  • 739
2 votes
1 answer
786 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?
Evan Carroll's user avatar
  • 63.6k
23 votes
2 answers
77k 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 "/...
Nyxynyx's user avatar
  • 1,121
18 votes
3 answers
21k 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) [...
Dr.YSG's user avatar
  • 399
7 votes
1 answer
4k 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 ...
yurtesen's user avatar
  • 237
3 votes
1 answer
822 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 ...
nenad007's user avatar
  • 153
3 votes
1 answer
756 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 ...
raph's user avatar
  • 33
3 votes
2 answers
2k 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 ...
Zia's user avatar
  • 169
2 votes
2 answers
2k 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 ...
Zia's user avatar
  • 169
2 votes
2 answers
757 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 ...
Alan Yu's user avatar
  • 145
2 votes
2 answers
177 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 ...
pookie's user avatar
  • 181
2 votes
1 answer
694 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 ...
J-DawG's user avatar
  • 283
1 vote
2 answers
406 views

PostGIS radius query

I have a table setup with the following columns, and it contains about 100K listing of data. Column: address | Type: Text Column: geo_point | Type: geometry(Point,4326) ...
user1187968's user avatar
0 votes
3 answers
1k views

How to SELECT "closest" rows from another table?

I have two tables, map1 and map2, there are multiple possible combinations between the columns map1.id1 and map2.id2. I have tried the below query: SELECT map1.id1, map2.id2, MIN(...
Gautam Shahi's user avatar
0 votes
1 answer
467 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 ...
Evan Carroll's user avatar
  • 63.6k
0 votes
1 answer
203 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 ...
Evan Carroll's user avatar
  • 63.6k
0 votes
2 answers
1k 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)...
Randomize's user avatar
  • 1,183
0 votes
1 answer
1k views

Just delete 50.000.000 of rows on a PostgreSQL table and DB still very slow

sorry for my poor english. I have a postgres DB runing on amazon RDS (db.t3.small), with django as a backend. i have made a mistake and created 50.000.000 rows. when i figure out (because queries on ...
Nahuel Fernandez's user avatar