SHOW rds.extensions;
CREATE EXTENSION postgis;
CREATE EXTENSION pgrouting;
SELECT PostGIS_full_version();

SELECT
  ogc_fid, name, iata_code, marker_size, marker_color, 
  marker_symbol, ST_AsGeoJSON(wkb_geometry) AS geojson
FROM public.airports
WHERE 
  ogc_fid = 3;

--   ST_GeometryType(geometry) returns the type of the geometry
--   ST_NDims(geometry) returns the number of dimensions of the geometry
--   ST_SRID(geometry) returns the spatial reference identifier number of the geometry
--   ST_X(geometry) returns the X ordinate
--   ST_Y(geometry) returns the Y ordinate
SELECT
  name,
  ST_GeometryType(wkb_geometry),
  ST_NDims(wkb_geometry),
  ST_SRID(wkb_geometry),
  ST_X(wkb_geometry),
  ST_Y(wkb_geometry),
  ST_AsText(wkb_geometry)
FROM public.airports
WHERE ogc_fid = 3;

-- Using ST_GeomFromText with the SRID parameter
SELECT ST_GeomFromText('POINT(-6.7982137 53.180124)', 4326);

-- Using ST_GeomFromText without the SRID parameter
SELECT ST_SetSRID(ST_GeomFromText('POINT(-6.7982137 53.180124)'), 4326);

-- Using a ST_Make* function
SELECT ST_SetSRID(ST_MakePoint(-6.7982137, 53.180124), 4326);

-- Using PostgreSQL casting syntax and ISO WKT
SELECT ST_SetSRID('POINT(-6.7982137 53.180124)'::geometry, 4326);

-- Using PostgreSQL casting syntax and extended WKT
SELECT 'SRID=4326;POINT(-6.7982137 53.180124)'::geometry;

-- https://postgis.net/workshops/postgis-intro/spatial_relationships.html
-- ST_Equals returns TRUE if two geometries of the same type have identical x,y coordinate values, 
-- i.e. if the second shape is equal (identical) to the first shape.
SELECT 
  name
FROM public.airports
WHERE
  ST_Equals(
    wkb_geometry,
    ST_SetSRID(ST_MakePoint(-8.493798524458427, 51.84173815), 4326)
  );

-- ST_Distance - Returns the minimum 2D Cartesian (planar) distance between two geometries
-- 
-- ST_Distance_Sphere — Returns minimum distance in meters between two lon/lat geometries. 
-- Uses a spherical earth and radius of 6370986 meters. Faster than ST_Distance_Spheroid, 
-- but less accurate.
--
-- ST_Distance_Spheroid — Returns the minimum distance between two lon/lat geometries given a particular spheroid. 
SELECT
  name,
  ROUND((ST_Distance(
      wkb_geometry::geography, 
      ST_SetSRID(ST_MakePoint(-6.7982137, 53.180124), 4326)::geography)/1000.0)::NUMERIC, 2) AS nearest_airport_km,
  ROUND((ST_Distance_Sphere(
      wkb_geometry, 
      ST_SetSRID(ST_MakePoint(-6.7982137, 53.180124), 4326))/1000.0)::NUMERIC, 2) AS nearest_airport_km_v2,
  ROUND((ST_Distance_Spheroid(
      wkb_geometry,
      ST_SetSRID(ST_MakePoint(-6.7982137, 53.180124), 4326),
      'SPHEROID["WGS 84",6378137,298.257223563]')/1000.0)::NUMERIC, 2) AS nearest_airport_km_v3
FROM airports
ORDER BY
  ST_Distance(
    wkb_geometry::geography,
    ST_SetSRID(ST_MakePoint(-6.7982137, 53.180124), 4326)::geography
  ) ASC;

SELECT
  ogc_fid, settlement, settl_name, guid,
  ST_AsGeoJSON(wkb_geometry) AS geojson
FROM public.settlements
WHERE
  settl_name = 'Sligo';

--   ST_Area(geometry) returns the area of the polygons
--   ST_NRings(geometry) returns the number of rings (usually 1, more of there are holes)
--   ST_ExteriorRing(geometry) returns the outer ring as a linestring
--   ST_InteriorRingN(geometry,n) returns a specified interior ring as a linestring
--   ST_Perimeter(geometry) returns the length of all the rings
--   ST_NumGeometries(geometry) returns the number of parts in the collection
--   ST_GeometryN(geometry,n) returns the specified part
--   ST_Area(geometry) returns the total area of all polygonal parts
--   ST_Length(geometry) returns the total length of all linear parts
--   ST_AsEWKT(geometry) returns Well-known text (WKT)
--   ST_AsEWKB(geometry) returns Well-known binary (WKB)
--   ST_AsGML(geometry) returns Geographic Mark-up Language (GML)
--   ST_AsKML(geometry) returns Keyhole Mark-up Language (KML)
--   ST_AsGeoJSON(geometry) returns GeoJSON
--   ST_AsSVG(geometry) returns Scalable Vector Graphics (SVG)
SELECT
  settlement,
  settl_name,
  ST_GeometryType(wkb_geometry),
  ST_Area(wkb_geometry),
  ST_NRings(wkb_geometry),
  ST_ExteriorRing(wkb_geometry),
  ST_InteriorRingN(wkb_geometry, 1),
  ST_Perimeter(wkb_geometry),
  ST_NumGeometries(wkb_geometry),
  ST_GeometryN(wkb_geometry,1),
  ST_Area(wkb_geometry),
  ST_Length(wkb_geometry),
  ST_AsText(wkb_geometry),
  ST_AsEWKT(wkb_geometry),
  ST_AsEWKB(wkb_geometry),
  ST_AsGML(wkb_geometry),
  ST_AsKML(wkb_geometry),
  ST_AsGeoJSON(wkb_geometry),
  ST_AsSVG(wkb_geometry)
FROM settlements
WHERE
  settl_name IN ('Gorey', 'Galway city and suburbs');

-- ST_Intersects(geometry A, geometry B) returns t (TRUE) if the two shapes have any space in common, i.e., 
-- if their boundaries or interiors intersect.
SELECT settl_name
FROM settlements
WHERE
  ST_Intersects(wkb_geometry, ST_SetSRID(ST_MakePoint(-6.7982137, 53.180124), 4326));
-- Returns Newbridge settlement

-- The opposite of ST_Intersects is ST_Disjoint(geometry A , geometry B). If two geometries are disjoint, they do not 
-- intersect, and vice-versa. In fact, it is often more efficient to test “not intersects” than to test “disjoint” 
-- because the intersects tests can be spatially indexed, while the disjoint test cannot.
-- For multipoint/polygon, multipoint/linestring, linestring/linestring, linestring/polygon, and 
-- linestring/multipolygon comparisons, ST_Crosses(geometry A, geometry B) returns t (TRUE) if the intersection 
-- results in a geometry whose dimension is one less than the maximum dimension of the two source geometries and 
-- the intersection set is interior to both source geometries.
SELECT count(settl_name)
FROM settlements
WHERE
  ST_Disjoint(wkb_geometry, ST_SetSRID(ST_MakePoint(-6.7982137, 53.180124), 4326));
-- Returns 872, all settlements except Newbridge

-- ST_Within(geometry A , geometry B) returns TRUE if the first geometry is completely within the second geometry. 
-- ST_Within tests for the exact opposite result of ST_Contains.
-- ST_Contains(geometry A, geometry B) returns TRUE if the second geometry is completely contained by the first geometry.
SELECT settl_name
FROM settlements
WHERE
  ST_Contains(wkb_geometry, ST_SetSRID(ST_MakePoint(-6.7982137, 53.180124), 4326));

SELECT settl_name
FROM settlements
WHERE
  ST_Within(ST_SetSRID(ST_MakePoint(-6.7982137, 53.180124), 4326), wkb_geometry);

SELECT
  ogc_fid, osm_id, name_tag, name_ga, name_en, alt_name,
  alt_name_g, logainm_re, osm_user, osm_timest, attributio,
  t_ie_url, area, latitude, longitude, epoch_tstm,
  stroke, stroke_opacity, stroke_width, fill, fill_opacity,
  ST_AsGeoJSON(wkb_geometry) AS geojson
FROM counties
WHERE
  name_tag = 'Tipperary';

SELECT
  counties.name_tag AS county_name,
  settlements.settl_name AS settlement_name
FROM settlements
JOIN counties
  ON ST_Contains(counties.wkb_geometry, settlements.wkb_geometry)
WHERE
  counties.name_tag = 'Dublin';

SELECT
  ogc_fid,
  guid,
  countyname,
  csoed,
  osied,
  edname,
  sa_pub2011,
  small_area,
  geogid,
  shape__area,
  shape__length,
  ST_AsGeoJSON(wkb_geometry) AS geojson
FROM small_areas
WHERE
  county = 'LS'
  AND edname = 'Abbeyleix'
  AND small_area = '107001003';

SELECT
  ogc_fid, id, name, type, line, zone, to_date(start, 'DD/MM/YYYY') AS start_date,
  stage, open, pnr, dublinbikes, ST_AsGeoJSON(wkb_geometry) AS geojson
FROM public.luas_stops
WHERE 
  id = 1;

CREATE TABLE public.e2001
(
  County VARCHAR(64),
  Sex VARCHAR(24),
  CensusYear INTEGER,
  DATA INTEGER
);

SELECT *
FROM public.e2001
WHERE
  county = 'Limerick'
  and sex = 'Both sexes';

-- There's over 800 columns in the dataset so just selecting a tiny subset. 
SELECT
  guid,
  geogid,
  geogdesc,
  t1_1age0m,
  t1_2t,
  t15_3_t
FROM sa_2016
WHERE 
  guid = '4c07d11e-11d3-851d-e053-ca3ca8c0ca7f';