28
Try this: Re: could not change directory to "/root":
Apparently you did "su postgres" from the root account, so you're
still in root's home directory. It'd be better to do "su - postgres"
to ensure you've acquired all of the postgres account's environment.
Reading "man su" might help you out here.
22
I've worked with both Postgres and SQL Server. I found Postgres to be superior in GIS functionality. And while I'm going to briefly detail my findings below, I'd suggest this: Give yourself a brief but reasonable time period to review the unfamiliar solution over the one you know, with specific goals in mind. For example, maybe a 2 week time period to ...
12
First, use
ST_SetSRID(ST_MakePoint(c.longitude, c.latitude),4326)::geography
instead of
ST_GeographyFromText('SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')')
Per documentation:
ST_MakePoint while not being OGC compliant is generally faster and
more precise than ST_GeomFromText and ST_PointFromText. It is also
easier to use if you ...
9
Answer to question
SELECT DISTINCT ON (name, zonedistrict_id)
ST_Union(geom) as geom, gid, name, zonedistrict_id, zonestyle_id, longname
FROM zones
GROUP BY gid, name, zonedistrict_id, zonestyle_id, longname
ORDER BY name, zonedistrict_id, zonestyle_id;
It depends on what you are actually trying to achieve and what version of Postgres you are ...
8
Have you set the environment variables to enable rasters ?
As of PostGIS 2.1.3, out-of-db rasters and all raster drivers are disabled by default. In order to re-enable these, you need to set the following environment variables: POSTGIS_GDAL_ENABLED_DRIVERS and POSTGIS_ENABLE_OUTDB_RASTERS in the server environment.
If you want to enable offline ...
8
You need to install a specific EXTENSION in your database:
CREATE EXTENSION btree_gist ;
According to PostgreSQL documentation on btree_gist:
btree_gist provides GiST index operator classes that implement B-tree equivalent behavior for the data types int2, int4, int8, float4, float8, numeric, timestamp with time zone, timestamp without time zone, time with ...
8
Assuming current Postgres 9.6, it can work.
Postgres can use indexes on a remote Postgres server. You may have to do more than for local tables, autovacuum does not run ANALYZE for remote tables automatically. Start by reading the chapter "Remote Query Optimization" in the manual.
Related answer with more details:
Postgres 9.5 foreign table inheritance ...
answered Jun 26 '17 at 15:49
Erwin Brandstetter
126k16 gold badges289 silver badges429 bronze badges
7
As a part of my work I maintain a fairly large PostgreSQL database (around 120gb on disk, several multi-million-row tables) and have collected a few tricks on how to speed up the queries. First some comments on your assumptions:
Yes, order is important, but it's only the first one that is really different, the rest are second class indexes.
I'm not sure it ...
7
I would recommend dumping the 9.1 database from your new 9.3 server like this:
pg_dump -h remoteserver -U remoteuser remotedbname -Fc -f my_old_server_backup.dump
I recommend using the 9.3 pg_dump as pg_dump is always backwards compatible, but not forward compatible. In other words, the newer pg_dump will take care of any syntax changes that the new server ...
6
PostGIS supports it with ST_GeoHash
ST_GeoHash — Return a GeoHash representation (geohash.org) of the geometry.
6
It seems that I had to re-run the AWS RDS PostGIS instructions:
CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;
SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO rds_superuser')
FROM (
SELECT nspname, relname
FROM pg_class c JOIN ...
6
OK, so you:
Dropped a bunch of constraints
Did some work
Attempted to add a completely unrelated random constraint and that failed
You only showed two of the constraints in the output (why?) but the two you showed were CHECK constraints, not UNIQUE constraints. So it makes absolutely no sense to attempt to replace them with a UNIQUE constraint.
You must:
...
6
Just an update to Dennis' comment from the AWS thread, this is now possible.
Quote from the thread:
We just launched RDS PostgreSQL 10.5 which supports libprotobuf-c.
https://aws.amazon.com/about-aws/whats-new/2018/10/rds-postgresql-supports-minor-versions/
The official release notes (with emphasis added):
[...]
With this update, we have also ...
5
Speaking of some very major things. Here is a list of things PostGIS supports that are totally absent in MySQL and MariaDB.
SRID in calculations, give your points a different SRID and you'll get different values back. This is prop
Aggregate functions: to the best of my knowledge MySQL offers no spatial aggregates functions
K nearest neighbor: Only PostGIS ...
5
A unique constraint creates a unique index to implement the constraint. The only index type in PostgreSQL that supports unique indexes is the default b-tree index type; you can't make a unique GIN or GiST index, e.g.:
regress=> create unique index indexname on test USING GiST(id) ;
ERROR: access method "gist" does not support unique indexes
Data types ...
5
Your query is needlessly complicated and can be untangled to this 100 % equivalent one:
SELECT gid, id, ST_MakeValid(geom) AS geom
FROM schema.table_polygons
WHERE geom IS NOT NULL;
Neither original nor this one remove any duplicates (except by possibly removing some with geom IS NULL, but I doubt that was your intention).
Possible misunderstanding
There ...
answered Jan 28 '16 at 10:00
Erwin Brandstetter
126k16 gold badges289 silver badges429 bronze badges
5
A couple of things that have caught you out here.
In the SQL Server version using the Point, the order of the coordinate is Lat Lon, eg Y X. The OGC Point construct is X Y or Lon Lat.
The next is that the PostGIS query you've posted actually returns 8.20039023523232e-005 which while appearing similar to the SQL Server result, is quite a lot smaller.
The ...
5
Background, functionality and performance
&& opperator
&& is bounding-box overlaps. All operators call functions in PostgreSQL: you can see this \doS+ && in this case && literally calls the PostGIS function geometry_overlaps. The only catch here is that && will make use of an index, from the docs
In general, you ...
5
First, use EXPLAIN ANALYZE (not mere EXPLAIN) and show the result of \d on the table.
(psql). As a first point this,
ST_GeographyFromText('POINT(47.4667 8.3167)')
should be written as ST_MakePoint(47.4667, 8.3167)::geography
Your problem here is this pattern,
SELECT ST_Distance( ST_MakePoint(47.4667, 8.3167)::geography, geo_pt2) AS dist
...
ORDER BY ...
5
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($${ ...
4
The column "row_num" doesn't exist because the logical order of processing requires the dbms to apply the WHERE clause before it evaluates the SELECT clause. The windowing function is part of the SELECT clause, so its alias isn't accessible in the same statement's WHERE clause.
The FROM clause is the very first part of the statement to be evaluated. That's ...
answered Apr 1 '13 at 0:56
Mike Sherrill 'Cat Recall'
9,4221 gold badge31 silver badges41 bronze badges
4
You seem to expect that rows with NULL values are excluded from a B-tree index automatically, but that's not the case. Those are indexed as well and can be searched for. However, since:
access_type ... is null in 90% of cases
that's hardly useful in your case. Such common values hardly ever make sense in an index to begin with, be it NULL or any other ...
answered Jul 19 '14 at 23:40
Erwin Brandstetter
126k16 gold badges289 silver badges429 bronze badges
4
As Craig explained very well, you cannot create a unique index on the type raster without all the necessary operators.
Your second best bet to enforce uniqueness is to create a functional index on the text representation:
CREATE UNIQUE INDEX us_tmin_enforce_scalex_rast
ON chp05.us_tmin (cast(rast as text))
Note that this cannot be implemented as ...
answered Aug 19 '14 at 16:21
Erwin Brandstetter
126k16 gold badges289 silver badges429 bronze badges
4
Best use ST_DWithin() in a LATERAL join:
SELECT t.*, neighbor.* -- or only selected columns
FROM tbl t
LEFT JOIN LATERAL (
SELECT *
FROM tbl t1
WHERE ST_DWithin(t.geo_point, t1.geo_point, 5000 * 1.609344)
) neighbor ON true
WHERE t.address = 'my_address'; -- to restrict to a particular address
1.609344 being the factor for converting ...
4
There are a few problems here.
The id column in your schema is not declared as unique. What would you do if you had two of the same ids? @a_horse_with_no_name reads this as greatest-n-per-group, but why the greatest? What's the criteria for?
INSERT INTO telemetry ( id, lat, lon ) VALUES (1,1,1);
INSERT INTO telemetry ( id, lat, lon ) VALUES (1,1,2);
You'...
4
I only need the distance to be accurate on a local scale and so curvature of the earth shouldn't be an issue for my application, but is there a performance benefit of either POINT or GIS in this context?
The native point is for geometry. If you want distance on Earth then those "points" are being projected onto a representation of Earth (sphereoid) and you ...
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
Off the top of my head, here are a few things to do, in no particular order.
VACUUM FREEZE
ANALYZE
Add the indexes that you think are going to help.
Execute as many queries as you can.
Look at pg_stat_user_indexes to see which queries are being used.
Look at the EXPLAIN ANALYZE plan for each query.
Review https://wiki.postgresql.org/wiki/...
3
The point of reference comes from the cafe in the center, so you can use a subquery to retrieve it from the addresses table instead of the manual input:
SELECT c.*, a.*, ST_Distance(t.lonlat, a.lonlat) AS distance -- pick columns you need
FROM addresses a
JOIN cafes c ON c.id = a.cafe_id
, (SELECT lonlat
FROM addresses
WHERE ...
3
UPDATE mytbl
SET col2=myfn(col1)
should do the trick
Only top voted, non community-wiki answers of a minimum length are eligible
