Function for getting Polygons by Bounding Box

[andrewxhill]

We are going to need a function that will take a bounding box (or any poly) and return a set of polygons that are inside of it.

This should be an Editor only function that allows someone to create a new table from the result. We don't have to worry about the "create new table" part at the beginning, we can do it by clicking the button in the editor. But having the function would allow us to demo cartodb without ever importing data.

I'd imagine

OBS_GetBoundariesByBBox(bbox)

returns a SETOF

{the_geom, unique_id}

such that the unique_id could also be used for later augmentation

cc @talos @stuartlynn @ohasselblad

[andrewxhill]

The same function should have a variant

OBS_GetPointsByBBox that would return something like st_pointonsurface

[andy-esch]

I'll take this since it relates to the other geom ones I wrote

[andy-esch]

for the signatures, i'm imagining:

OBS_GetGeometriesByBBox(geom_in geometry, boundary_id text, timespan text)
RETURNS table(geom_out geometry, geometry_reference text)

And for getting back data from an existing table, something like this:

SELECT geoms.the_geom, geoms.geom_ref
FROM (
  SELECT OBS_GetGeometryByBBox(polygon_geoms, '"us.census.tiger".census_tract') As geoms
  FROM original_table
) As x

Or just

SELECT *
FROM OBS_GetGeometriesByBBox(bounding_box_geom)

[andrewxhill]

actually, stick to Boundaries though for terminology

On Wed, Apr 27, 2016 at 3:10 PM, Andy Eschbacher notifications@github.com wrote:

for the signatures, i'm imagining:

OBS_GetGeometriesByBBox(geom_in geometry, boundary_id text, timespan text) RETURNS table(geom_out geometry, geometry_reference text)

And for getting back data from an existing table, something like this:

SELECT geoms.the_geom, geoms.geom_refFROM ( SELECT OBS_GetGeometryByBBox(the_geom, '"us.census.tiger".census_tract') As geoms FROM original_table ) As x

— You are receiving this because you authored the thread. Reply to this email directly or view it on GitHub https://github.com/CartoDB/observatory-extension/issues/35#issuecomment-215196679

andrewxhill http://twitter.com/andrewxhill http://cartodb.com

[andy-esch]

https://github.com/CartoDB/observatory-extension/pull/30/commits/6ed903570b3b4695834455aea045cf2f7198d034

[andrewxhill]

great!

only thing, moving forward try not to develop new things on open PRs, we should do them as new PRs

[andy-esch]

we rolled back those two commits before merging into develop and i have the functions in a new feature branch now: https://github.com/CartoDB/observatory-extension/commit/1a19e338770458a6b52a639a37d706d8915b4809

[andrewxhill]

slick, thanks for the new PR

[andrewxhill]

great work on these, so much poooower