Skip to content

Instantly share code, notes, and snippets.

@sagarchauhan005

sagarchauhan005/veu-search-query.sql

Created April 30, 2020 07:27
Download ZIP

Revisions

  1. sagarchauhan005 created this gist Apr 30, 2020.
    18 changes: 18 additions & 0 deletions veu-search-query.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,18 @@
    select * from <table> where
    ST_CONTAINS(
    ST_GEOMFROMTEXT(
    CONCAT(
    'POLYGON((
    ', json_unquote(json_extract(column_name, '$.rectangle.bounds."north-east".lng')), '
    ', json_unquote(json_extract(column_name, '$.rectangle.bounds."north-east".lat')), ',
    ', json_unquote(json_extract(column_name, '$.rectangle.bounds."north-west".lng')), '
    ', json_unquote(json_extract(column_name, '$.rectangle.bounds."north-west".lat')), ',
    ', json_unquote(json_extract(column_name, '$.rectangle.bounds."south-west".lng')), '
    ', json_unquote(json_extract(column_name, '$.rectangle.bounds."south-west".lat')), ',
    ', json_unquote(json_extract(column_name, '$.rectangle.bounds."south-east".lng')), '
    ', json_unquote(json_extract(column_name, '$.rectangle.bounds."south-east".lat')), ',
    ', json_unquote(json_extract(column_name, '$.rectangle.bounds."north-east".lng')), '
    ', json_unquote(json_extract(column_name, '$.rectangle.bounds."north-east".lat')), '
    ))'
    )
    ), POINT('.$long.', '.$lat.'));