-----------------------
create extension postgis;
-----------------------
CREATE TABLE artwork (
artwork_name            TEXT,
category            VARCHAR(20),
artist_name             VARCHAR (50),
showed_at_latitude      FLOAT,
showed_at_longitude     FLOAT,
where_is            GEOGRAPHY );
-----------------------
INSERT INTO artwork VALUES ('Giaconda','painting','Leonardo Da Vinci', 48.860547, 2.338513,NULL);
INSERT INTO artwork VALUES ('David','sculpture','Michelangelo Buonarroti', 43.776709, 11.258887,NULL);
INSERT INTO artwork VALUES ('Sunflowers','painting','Vincent Van Gogh', 48.149966, 11.570856,NULL);
INSERT INTO artwork VALUES ('Guernica',' painting','Pablo Picasso', 40.407561, -3.694042,NULL);
-----------------------
UPDATE artwork SET where_is = ST_POINT(showed_at_longitude, showed_at_latitude);
SELECT * FROM artwork;
-----------------------
#### Add an extra artwork in Athens
INSERT INTO artwork VALUES ('Periklis','sculpture','Feidias', 37.9588039,23.7189003,NULL);
UPDATE artwork SET where_is = ST_POINT(showed_at_longitude, showed_at_latitude) WHERE artwork_name = 'Periklis';
SELECT * FROM artwork;
-----------------------
SELECT  artwork1.artwork_name,
        artwork2.artwork_name,
        ST_DISTANCE(artwork1.where_is, artwork2.where_is)
FROM    artwork artwork1,
        artwork artwork2
-----------------------
CREATE TABLE museum (
    museum_name     VARCHAR(20),
    country         VARCHAR(20),
    perimeter       GEOGRAPHY(POLYGON)
);
-----------------------
INSERT INTO museum VALUES ('Munich Pinakotek','Deuschtland',ST_GeogFromText('POLYGON((11 48,11 49,12 49,12 48,11 48))'));
INSERT INTO museum VALUES ('Accademia Gallery','Italy',ST_GeogFromText('POLYGON((11 43,11 44,12 44,12 43,11 43))'));
INSERT INTO museum VALUES ('Musee du Louvre','France',ST_GeogFromText('POLYGON((2 48,2 49,3 49,3 48,2 48))'));
INSERT INTO museum VALUES ('Museo Reina Sofia','Spain',ST_GeogFromText('POLYGON((-4 40,-4 41,-3 41,-3 40,-4 40))'));
-----------------------
SELECT  museum_name,
        st_area(perimeter)
FROM museum
ORDER BY st_area DESC
-----------------------
SELECT  artwork_name,
        museum_name
FROM    museum,
        artwork
WHERE   ST_INTERSECTS(where_is, perimeter)
-----------------------
SELECT ST_SRID(ST_GeomFromText('POINT(-71.1043 42.315)',4326));
SELECT ST_SetSRID(ST_Point(-123.365556, 48.428611),4326)
-----------------------
ALTER TABLE museum ADD COLUMN point geography(Point);
ALTER TABLE museum DROP COLUMN point;
-----------------------
SELECT ST_PointFromText('POINT(-71.064544 42.28787)', 4326);
SELECT ST_GeomFromText('LINESTRING(-71.160281 42.258729,-71.160837 42.259113,-71.161144 42.25932)',4269);
SELECT ST_LineFromText('LINESTRING(1 2, 3 4)') 
SELECT ST_PolygonFromText('POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866 42.3903701743239, -71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917 42.3902909739571))');
SELECT ST_MakePolygon(ST_GeomFromText('LINESTRING(75.15 29.53,77 29,77.6 29.5, 75.15 29.53)'));
-----------------------
SELECT GeometryType(ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)'));
SELECT ST_AsText(ST_Boundary(ST_GeomFromText('POLYGON((1 1,0 0, -1 1, 1 1))')));
SELECT ST_AsText(ST_Boundary(ST_GeomFromText('LINESTRING(1 1,0 0, -1 1)')));
-----------------------
SELECT ST_AsText(ST_Envelope('LINESTRING(0 0, 1 3)'::geometry));
SELECT ST_AsText(ST_Envelope('POLYGON((0 0, 0 1, 1.0000000001 1, 1.0000000001 0, 0 0))'::geometry));
SELECT ST_CoordDim('CIRCULARSTRING(1 2 3, 1 3 4, 5 6 7, 8 9 10, 11 12 13)');
-----------------------
SELECT ST_AsBinary(ST_GeomFromText('POLYGON((0 0,0 1,1 1,1 0,0 0))',4326));
SELECT ST_IsEmpty(ST_GeomFromText('GEOMETRYCOLLECTION EMPTY'));
SELECT ST_IsEmpty(ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 1 2))'));
-----------------------
SELECT ST_Equals(ST_GeomFromText('LINESTRING(0 0, 10 10)'), ST_GeomFromText('LINESTRING(0 0, 5 5, 10 10)'));
SELECT ST_Disjoint('POINT(0 0)'::geometry, 'LINESTRING ( 2 0, 0 2 )'::geometry);
SELECT ST_Disjoint('POINT(0 0)'::geometry, 'LINESTRING ( 0 0, 0 2 )'::geometry);
SELECT ST_Intersects('POINT(0 0)'::geometry, 'LINESTRING ( 2 0, 0 2 )'::geometry);
SELECT ST_Intersects('POINT(0 0)'::geometry, 'LINESTRING ( 0 0, 0 2 )'::geometry);
SELECT ST_Intersects(
		ST_GeographyFromText('SRID=4326;LINESTRING(-43.23456 72.4567,-43.23456 72.4568)'),
		ST_GeographyFromText('SRID=4326;POINT(-43.23456 72.4567772)')
		);
SELECT ST_Touches('LINESTRING(0 0, 1 1, 0 2)'::geometry, 'POINT(0 2)'::geometry);
select ST_AsText(ST_Union('POINT(1 2)' :: geometry, 'POINT(-2 3)' :: geometry))
----- distance in meters
SELECT ST_Distance(
			ST_Transform('SRID=4326;POINT(-72.1235 42.3521)'::geometry, 3857),
			ST_Transform('SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry, 3857)
		);
-----------------------