Oh forgot one more very useful operator, the subtraction operator. Removes a
key/value from the list:
SELECT '{"color": "blue", "height_m": 10}'::jsonb - 'color'
Returns:
{"height_m": 10}
From: Regina Obe [mailto:l...@pcorp.us]
Sent: Friday, January 7, 2022 11:18 PM
To: 'PostGIS
David,
Not sure what you are asking? There are many functions for jsonb and even more
the newer your PostgreSQL is.
Take a look at - https://www.postgresql.org/docs/current/functions-json.html
You can pull arrays by index but not really key/values by index (because jsonb
reorders
Hi, Regina,
That is interesting!
How to add new entries to the dictionary. E.g., weight: 40?
Can the dictionary to serve as a collection of paired key, value set, so
that we can accumulate data to be processed?
Then, we can deal with the first, then 2nd and so on in turn?
Or, we can do
As Raj mentioned, it’s backward compatible.
The main thing that changed between 2.5.2 and 3 is that the postgis_raster
extension is split so make sure you follow the additional steps for that as it
unbundles the raster functions from PostGIS and rebundles them. I forget how
we got around
Am 07.01.2022 um 21:55 schrieb Luca Bertoncello:
> Does someone have an idea?
OK, I got it with a function:
CREATE OR REPLACE FUNCTION getElevationsOnLine(startPoint geometry,
endPoint geometry, pointsInterval int)
RETURNS TABLE (
lat DOUBLE PRECISION,
lng DOUBLE PRECISION,
elevation
Use JSONB datatype.
CREATE TABLE test(id integer, data jsonb);
TRUNCATE tABLE test;
INSERT INTO test(id, data)
VALUES (1, '{"color": "red", "height_m": 10}');
-- PG14 or higher – you can used subscript feature
UPDATE test SET data['color'] = to_jsonb('blue'::text),
Am 07.01.2022 um 21:31 schrieb Luca Bertoncello:
> Am 07.01.2022 um 21:28 schrieb Paul Ramsey:
>> Yes, the "distances" you got when you ran the calculation in geometry space
>> were wrong. They were both wrong in unit terms (meaningless units vs meters)
>> and also in picking the right elevation
Am 07.01.2022 um 21:28 schrieb Paul Ramsey:
> Yes, the "distances" you got when you ran the calculation in geometry space
> were wrong. They were both wrong in unit terms (meaningless units vs meters)
> and also in picking the right elevation point (since there's a N/S
> compression effect in
Yes, the "distances" you got when you ran the calculation in geometry space
were wrong. They were both wrong in unit terms (meaningless units vs meters)
and also in picking the right elevation point (since there's a N/S compression
effect in raw lon/lat coordinates working in cartesian space).
Am 07.01.2022 um 21:20 schrieb Paul Ramsey:
> Yep, I cut'n'pasted the wrong example.
>
>
> EXPLAIN ANALYZE
> SELECT *,
> latlng <-> '010120E610E17A14AE47412C40BC74931804A64940' AS dist
> FROM elevation2x2
> ORDER BY dist LIMIT 5;
OK, this works very fast, but I think it is not
Yep, I cut'n'pasted the wrong example.
EXPLAIN ANALYZE
SELECT *,
latlng <-> '010120E610E17A14AE47412C40BC74931804A64940' AS dist
FROM elevation2x2
ORDER BY dist LIMIT 5;
> On Jan 7, 2022, at 12:19 PM, Luca Bertoncello wrote:
>
> Am 07.01.2022 um 20:53 schrieb Paul Ramsey:
>> You
Am 07.01.2022 um 20:53 schrieb Paul Ramsey:
> You have an index on the geography, but you cast to geometry in your distance
> calc, so the index helps you not at all.
>
> Do this
>
> SELECT *, ST_Distance(latlng,
> '010120E610E17A14AE47412C40BC74931804A64940') AS dist FROM
> elevation
>
> Well, this was a very hard job...
> I got the EU-DEM data from
> https://land.copernicus.eu/imagery-in-situ/eu-dem/eu-dem-v1.1?tab=download
> Then I converted the 38GB TIFFs in an SQL database. First of all, I
> reduced the precision, since 25 meter is for flying too much precision.
> One or
You have an index on the geography, but you cast to geometry in your distance
calc, so the index helps you not at all.
Do this
SELECT *, ST_Distance(latlng,
'010120E610E17A14AE47412C40BC74931804A64940') AS dist FROM
elevation ORDER BY dist LIMIT 5;
> On Jan 7, 2022, at 11:18 AM, Luca
Am 07.01.2022 um 20:35 schrieb Paul Ramsey:
Hi Paul
> Use the nearest neighbor operator
>
> https://www.postgis.net/workshops/postgis-intro/knn.html
Even too long... About 16 seconds...
I think I should create an index?
gis=# explain analyze SELECT *, latlng::geometry <->
Use the nearest neighbor operator
https://www.postgis.net/workshops/postgis-intro/knn.html
Do you have a link to your source data? The table of elevations?
> On Jan 7, 2022, at 11:18 AM, Luca Bertoncello wrote:
>
> Am 07.01.2022 um 20:01 schrieb Luca Bertoncello:
>
> Hi again
>
>> I get 33
Am 07.01.2022 um 20:01 schrieb Luca Bertoncello:
Hi again
> I get 33 points as result, and since the distance between the both
> aerodromes is about 31 km, it could be correct...
>
> Now I'm not very sure how to get the nearest point in the table for all
> these points...
> Maybe it's just
Am 07.01.2022 um 19:28 schrieb Martin Davis:
Hi Martin,
> How about this for an approach?
>
> - Create a line from the start and end point using ST_MakeLine
> - Densify it using ST_Segmentize (e.g. to a1 km interval)
> - Extract the individual points using ST_DumpPoints
> - For each point,
How about this for an approach?
- Create a line from the start and end point using ST_MakeLine
- Densify it using ST_Segmentize (e.g. to a1 km interval)
- Extract the individual points using ST_DumpPoints
- For each point, query to find the nearest elevation point
On Thu, Jan 6, 2022 at 12:51
pgAdmin is not supposed to a be a full fledged GIS, it is an
administrative tool to manage your (spatial) databases, that just
happens to have some very rudimentary spatial visualization capability.
If you need GIS type symbolization and labelling functionality, install
QGIS, it has everything
20 matches
Mail list logo
