Solve TSP with postgres extension (optional coba-coba)

[alrasyidlathif]

fetch data-data yang ada dalam radius tertentu dari suatu titik

create extension postgis;
create table restaurants (id int,name varchar(100), lat_long geometry);
create index idx_restaurants_lat_long on restaurants using gist((lat_long::geography));

insert into restaurants values(1,'Nisarg',ST_GeomFromText('Point(89.9999 10.0001)'));
select * from restaurants where ST_DWithin(lat_long,cast(ST_SetSRID(ST_MakePoint(89.9999,10.0001),4326) as geography),1000);

source: https://medium.com/@sarvesh10n/efficient-technique-to-fetch-data-based-on-location-7d1caa261867

[alrasyidlathif]

fetch data with order by nearest from a point

CREATE EXTENSION earthdistance CASCADE;

SELECT *, ROUND(earth_distance(ll_to_earth(42.1, 19.1), ll_to_earth(lat, lng))::NUMERIC, 2) AS distance
FROM
 cities
WHERE
 earth_box(ll_to_earth (42.1, 19.1), 10000) @> ll_to_earth (lat, lng)
 AND earth_distance(ll_to_earth (42.1, 19.1), ll_to_earth (lat, lng)) < 10000
ORDER BY
 distance;

1. ll_to_earth(latitude, longitude) — translates latitude, longitude pair into earth type, calculating 3D point location
2. earth_distance(point1, point2) — computes the distance between two points in earth format
3. earth_box(point, radius) — creates a bounding cube, sized to contain all the points that are not farther than radius meters from a given point. This cube is an approximation, but we can use it for search with @> operator (containment). This operator efficiently supports GIST indexes, but we need to refine the result

source: https://postindustria.com/postgresql-geo-queries-made-easy/

[alrasyidlathif]

solve TSP given a distance matrix with postgres

create extension postgis;
create extension pgrouting;

pgr_dijkstra(Edges SQL, start_vid, end_vid)
RETURNS SET OF (seq, path_seq, node, edge, cost, agg_cost) or EMPTY SET

example:
SELECT * FROM pgr_dijkstra(
    'SELECT id, source, target, cost, reverse_cost FROM edge_table',
    2, 3
);

result:
 seq | path_seq | node | edge | cost | agg_cost
-----+----------+------+------+------+----------
   1 |        1 |    2 |    4 |    1 |        0
   2 |        2 |    5 |    8 |    1 |        1
   3 |        3 |    6 |    9 |    1 |        2
   4 |        4 |    9 |   16 |    1 |        3
   5 |        5 |    4 |    3 |    1 |        4
   6 |        6 |    3 |   -1 |    0 |        5
(6 rows)

source: https://docs-pgrouting-org.translate.goog/3.1/en/pgr_dijkstra.html?_x_tr_sl=en&_x_tr_tl=id&_x_tr_hl=id&_x_tr_pto=tc