Skip to content

Instantly share code, notes, and snippets.

@rdlabo

rdlabo/geo.sql

Created March 2, 2020 02:53
Show Gist options
  • Save rdlabo/7e10b25589b8117188b6b1302f063556 to your computer and use it in GitHub Desktop.
Save rdlabo/7e10b25589b8117188b6b1302f063556 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
geo.sql
alter table tweet drop index geom;
alter table tweet modify geom geometry not null SRID 4326 ;
alter table tweet add spatial index(geom);
UPDATE tweet
SET
geom = CASE
WHEN
area_type = 1
THEN
ST_GEOMFROMTEXT(ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(139.7690174, 35.6803997, 8),
4326)),
4326)
WHEN
area_type = 2
THEN
ST_GEOMFROMTEXT(ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(135.5022535, 34.6937249, 8),
4326)),
4326)
ELSE ST_GEOMFROMTEXT(ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(137.292389, 35.0182505, 8),
4326)),
4326)
END
WHERE
1 = 1;
explain SELECT
id
FROM
tweet
WHERE
ST_Within(
tweet.geom,
ST_GeomFromText(St_AsText(ST_Buffer(ST_GeomFromText(St_AsText(ST_GeomFromText('POINT(35.668440825448 139.74229644907)' , 4326))),0.0089831528411952)),4326)
)
@rdlabo
Copy link
Author

rdlabo commented Mar 2, 2020

最新版

SELECT 
    COUNT(*)
FROM
   location_tweet
WHERE
    ST_WITHIN(location_tweet.geom,
            ST_GEOMFROMTEXT(ST_ASTEXT(ST_BUFFER(ST_GEOMFROMTEXT(ST_ASTEXT(ST_POINTFROMGEOHASH('xn324', 4326))),
                                    180.0 / 3.141592653589793 / 6378137.0 * 200000)),
                    4326))

@rdlabo
Copy link
Author

rdlabo commented Mar 2, 2020

関数を使わないでポリゴンを指定した版

SELECT 
    COUNT(*)
FROM
    tipsys.location_tweet
WHERE
    ST_WITHIN(location_tweet.geom,
            ST_GEOMFROMTEXT('POLYGON((36.99663056823905 136.87,36.96210881565135 137.22050523610176,36.85987020948018 137.55754075254575,36.693843722024624 137.8681544634467,36.470409658088876 138.14040965808886,36.19815446344671 138.36384372202463,35.88754075254577 138.52987020948018,35.550505236101785 138.63210881565135,35.20000000000001 138.66663056823904,34.849494763898235 138.63210881565135,34.512459247454245 138.52987020948018,34.2018455365533 138.36384372202463,33.92959034191114 138.14040965808888,33.70615627797539 137.8681544634467,33.54012979051983 137.55754075254578,33.43789118434866 137.2205052361018,33.40336943176096 136.87,33.43789118434866 136.51949476389822,33.54012979051983 136.18245924745423,33.70615627797538 135.8718455365533,33.92959034191114 135.59959034191112,34.2018455365533 135.37615627797538,34.512459247454245 135.21012979051983,34.84949476389823 135.10789118434866,35.2 135.07336943176097,35.55050523610178 135.10789118434866,35.88754075254576 135.21012979051983,36.198154463446706 135.37615627797538,36.47040965808887 135.59959034191112,36.693843722024624 135.8718455365533,36.85987020948018 136.18245924745423,36.96210881565135 136.51949476389822,36.99663056823905 136.87))',
                    4326))

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment