Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create extension if not exists postgis;
select postgis_full_version();

CREATE EXTENSION




postgis_full_version


POSTGIS="3.4.0 0874ea3" [EXTENSION] PGSQL="160" GEOS="3.11.1-CAPI-1.17.1" PROJ="9.1.1 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org USER_WRITABLE_DIRECTORY=/tmp/proj DATABASE_PATH=/usr/share/proj/proj.db" LIBXML="2.9.14" LIBJSON="0.16" LIBPROTOBUF="1.4.1" WAGYU="0.5.0 (Internal)"



SELECT 1


create table base_stations (
  id int generated by default as identity primary key,
  longitude double precision, 
  latitude double precision);
insert into base_stations values
(1,0,0),
(2,2,1),
(3,3,2),
(4,4,3)
returning *;

create table ptp_links (
  id int generated by default as identity primary key,
  in_base_station int, 
  out_base_station int,
  link_purpose text default 'backhaul',
  in_address_geo_x double precision,
  in_address_geo_y double precision,
  geom geometry(linestring,4326));
insert into ptp_links (in_base_station,out_base_station) values
(1,2),
(2,3),
(3,4)
returning *;

CREATE TABLE




id
longitude
latitude


1
0
0

2
2
1

3
3
2

4
4
3



INSERT 0 4



CREATE TABLE




id
in_base_station
out_base_station
link_purpose
in_address_geo_x
in_address_geo_y
geom


1
1
2
backhaul
null
null
null

2
2
3
backhaul
null
null
null

3
3
4
backhaul
null
null
null



INSERT 0 3


WITH update_batch AS (
    SELECT ptp.id,--something unique to targeted record
      ST_MAKELINE(
        ST_POINT(out_bs.longitude::DOUBLE PRECISION,
                 out_bs.latitude::DOUBLE PRECISION, 
                 4326),
        ST_POINT(CASE WHEN ptp.link_purpose = 'backhaul'
          THEN in_bs.longitude::DOUBLE PRECISION
          ELSE ptp.in_address_geo_x::DOUBLE PRECISION
        END,
        CASE WHEN ptp.link_purpose = 'backhaul'
          THEN in_bs.latitude::DOUBLE PRECISION
          ELSE ptp.in_address_geo_y::DOUBLE PRECISION
        END, 4326)
      ) AS new_geom
    FROM ptp_links ptp
    LEFT JOIN base_stations in_bs ON ptp.in_base_station = in_bs.id
    LEFT JOIN base_stations out_bs ON ptp.out_base_Station = out_bs.id )
UPDATE ptp_links AS target
SET geom = new_geom
FROM update_batch AS source
WHERE source.id = target.id
RETURNING target.id,in_base_station,out_base_station,link_purpose,st_astext(geom);


id
in_base_station
out_base_station
link_purpose
st_astext


1
1
2
backhaul
LINESTRING(2 1,0 0)

2
2
3
backhaul
LINESTRING(3 2,2 1)

3
3
4
backhaul
LINESTRING(4 3,3 2)



UPDATE 3

id	in_base_station	out_base_station	link_purpose	in_address_geo_x	in_address_geo_y	geom
1	1	2	backhaul	null	null	null
2	2	3	backhaul	null	null	null
3	3	4	backhaul	null	null	null

id	in_base_station	out_base_station	link_purpose	st_astext
1	1	2	backhaul	LINESTRING(2 1,0 0)
2	2	3	backhaul	LINESTRING(3 2,2 1)
3	3	4	backhaul	LINESTRING(4 3,3 2)