Hot answers tagged

4

You might want to consider doing some Requirements Engineering for your project. When you have all the requirements listed, then you will want to write it up in a document and have companies like Oracle, Microsoft, IBM or others offer you a solution. I don't think an open-source solution will work for the amount of data you are expecting without having ...


3

One thing that sometimes works is to rewrite a series of OR conditions into a sequence of UNIONs: CREATE TABLE restriction AS SELECT '100' as to_cost, a.nextedge as target_id, a.currentedge as via_path FROM adjacency a JOIN adjacency b ON a.nextedge = b.currentedge WHERE a.sourcenode = b.sourcenode AND (a.degrees < 45.0 OR a.degrees > 315.0) UNION ...


2

I personally think this is not a good design because all intermediate stop points must be included into two segments. IMHO, you should be able to transform it into something similar to: id | start | end | stop ---|-------|-----|----- 1 | 1 | 2 | 2 | 2 | 3 | yes 3 | 3 | 4 | 4 | 4 | 5 | 5 | 5 | 6 | yes But using your ...


2

You can create a multi-column index on spatial and time data. create extension btree_gist; create index on data using gist (ts, pos); With GiST, you generally want the column which is used more-selectively in the query to be first in the index. I suspect that that is "ts", but I don't know for sure. You might want to try it both ways. A multicolumn BRIN ...


1

You have to put the literal strings in the right spot. You have them as part of the FROM clause, which doesn't make sense. Put them in the select-list. insert into locations (geom,type,label,user) select st_makeline(a.geom,b.geom) as line, 'Link','test','ADMIN' from (select * from locations where id = 144) as a, (select * from locations where ...


1

FROM ... JOIN stations AS subways ... This is called an alias. You aliased stations as subways. So for the rest of the query (ON, SELECT part) the table is called subways and you can only reference it as subways.


1

The below worked for what I needed: SELECT MAX(char_length(column1)) as column1, MAX(char_length(column2)) as column2 FROM schema.table


1

This error is a result of you not having access on that view. What you need to run to fix the permissions is ALTER TABLE public.spatial_ref_sys OWNER TO rds_superuser; GRANT SELECT, INSERT ON TABLE public.spatial_ref_sys TO public; Found here on the AWS forums


1

This would achieve it: SELECT m1.id1, m2.* FROM map1 m1 CROSS JOIN LATERAL ( SELECT ST_HausdorffDistance(m1.g1, m2.g2) AS h_dist, m2.id2, m2.g2 FROM map2 m2 WHERE ST_HausdorffDistance(m1.g1, m2.g2) < 2 ORDER BY 1, 2 LIMIT 2 ) m2; Returns 1 or 2 rows for every row in map1, extended with the top 2 corresponding row(s) in map2 (as ...


1

raster2pgsql This a helper to make the job easy. It's not strictly speaking required. raster2pgsql is a raster loader executable that loads GDAL supported raster formats into sql suitable for loading into a PostGIS raster table. It is capable of loading folders of raster files as well as creating overviews of rasters. You can just redirect the output ...


1

From the docs on ST_GeomFromGeoJSON ST_GeomFromGeoJSON works only for JSON Geometry fragments. It throws an error if you try to use it on a whole JSON document. This means you'll want to cut out just the value from the geometry in your GeoJSON document, SELECT ST_AsText(geom) FROM ST_GeomFromGeoJSON($${ ...


1

You can unnest the array values using json_array_elements function and ->> operator: create table t (data json); insert into t values ('{"points": [ {"lat": 45.50884, "lng": -73.58781, "info": "x1"}, {"lat": 41.56355, "lng": -81.57327, "info": "x2"}, {"lat": 41.52335, "lng": -81.57423, "info": "x3"}]}'::json); select points->>'lat'...


1

I suggest to calculate the intersection of the geometries beforehand only once and store it in an additional table evidensapp_seniangcbr_polystructures_intersection containing an id, evidensapp_seniangcbr_id pointing to evidensapp_seniangcbr.id and evidensapp_polystructures_id pointing to evidensapp_polystructures.id. Then you can get rid of the geometry ...


1

As @a_horse_with_no_name wrote pg_dump and pg_restore cope with PostGIS things without problems. At least since PostGIS 2.0, previously (before PostGIS became an extension) you might had to change paths of the c-library used for geometry-functions. In PostgreSQL since 9.x ist is also fairly easy: backup at the original databse with pg_dump at the new ...


1

For me this did the trick, pay attention to quotes (') sudo -Hiu postgres 'pg_dump --column-inserts --data-only --table=someTable entities_db > /var/backups/anywhere/$(date +%Y%m%d_%H%M%S)_someTable.sql' Note the -Hiufor sudo, or use su - postgres you can also put that in a cronjob for root with crontab -e


Only top voted, non community-wiki answers of a minimum length are eligible