PLUTO reverse geocode SQL

100%

PLUTO reverse geocode SQL

✓ Published0🌍 Public

Last edited Aug 14, 2013

Created on Aug 14, 2013

This example demonstrates a SQL function that reverse geocodes a pair of latitude and longitude coordinates against New York City’s PLUTO parcel dataset. It returns up to three nearby addresses, owner names, and their straight-line distances from the input point. The function, written in SQL and using PostGIS spatial functions, first queries the `nyc_mappluto_13v1` table for the 20 nearest geometries via the `CDB_LatLng` and the `<->` operator, then filters and sorts those candidates by distance. It uses `ST_Distance` with a geography cast to compute metric distances.

AI-generated description

Similar vizzes

Loading thumbnail…

parenthesis enclosed NYC open data lat/lon to the_geom

A SQL snippet converts NYC Open Data latitude/longitude coordinates stored as parenthesized text in a column into the `the_geom` geometry field for PostGIS. The example shows how to parse a string like `(lat, lon)` by stripping parentheses, splitting on commas, and reordering the values into `ST_MakePoint` before applying `ST_SetSRID` with EPSG:4326. This enables spatial queries and map rendering from raw tabular coordinate data. The command targets a placeholder table and column, making it adaptable to any dataset with similar text-encoded points.

Aandrewxhill

66% match

Loading thumbnail…

Grid snapping only in SQL!

This example demonstrates how to snap a hand-drawn path to a street grid using only SQL and PostGIS spatial functions. It takes an input line geometry, finds the nearest high-order road segments from New York OpenStreetMap data, and iteratively interpolates points along those roads to create a new, grid-aligned linestring. The process uses `ST_PointN`, `ST_Line_Interpolate_Point`, `line_locate_point`, and the `ORDER BY <->` operator for nearest-neighbor queries, building the snapped result by alternating between forward and reverse passes.

Aandrewxhill

62% match

Loading thumbnail…

cdb_latlng

This example defines a set of PostgreSQL functions for converting latitude and longitude values into spatial geometries. It shows how to construct valid 4326-point geometries from numeric, floating-point, or text inputs, including support for comma-style decimal separators. The code uses PostGIS functions such as `ST_SetSRID` and `ST_MakePoint` to build the geometry, along with custom helpers like `CDB_DecimalDegreeLiteral` for parsing text coordinates. It handles both individual lat/lng arguments and a single space-separated coordinate string, making it a flexible utility for geospatial data preprocessing in SQL.

Aandrewxhill

61% match

Loading thumbnail…

PLPGSQL Function to cross the dateline

This example provides a PL/pgSQL function, `AXH_CrossDateLine`, that constructs a valid geometry for a line crossing the antimeridian in Web Mercator projection (EPSG:3857). It takes two input points, identifies the western and easternmost coordinates, and calculates the latitude where the line intersects the projection’s edge using proportional math. The function then returns a multi-part line by splitting the path at the ±20037508.34 meter boundaries, using PostGIS functions like `ST_MakeLine`, `ST_SetSRID`, and `ST_Collect`. This utility ensures dateline-crossing routes render correctly in web maps.

Aandrewxhill

60% match

Loading thumbnail…

Gist 89811213f98645c51fa8

Using CartoDB’s SQL API and PostGIS functions, this notebook explores Gilt purchase data aggregated by zip code. It shows a series of interactive maps, including a bubble map of average session time and choropleths of purchase rates, built from geocoded postal points and polygon tables. The workflow demonstrates nested SQL subqueries to join transaction aggregates with geometries, spatial filtering with `ST_Intersects` and `ST_Buffer` around New York City, and on-the-fly centroid generation via `st_centroid` for a target audience map. Geocoding and tile rendering rely on CartoDB’s default web-mercator projection.

Aandrewxhill

60% match

Loading thumbnail…

mongodb geospatial tutorial

This example demonstrates MongoDB’s geospatial query capabilities for locating restaurants within New York City neighborhoods. It shows how to find a neighborhood containing a given point, count restaurants inside that neighborhood, and filter restaurants by proximity using spherical distance calculations. The code uses MongoDB’s `2dsphere` indexes, `$geoIntersects`, `$geoWithin`, `$centerSphere`, and `$nearSphere` operators against imported GeoJSON datasets (`neighborhoods.json` and `restaurants.json`). It also illustrates the earth’s radius in kilometers and miles for distance thresholds.

Aaaizemberg

59% match

Loading thumbnail…

PlpgSQL to cross dateline when drawing two points

This example demonstrates a PostGIS/PLpgSQL function that corrects lines crossing the 180-degree antimeridian when drawn on a web Mercator map. It takes two point geometries and constructs a MultiLineString that breaks the crossing segment at the map’s edge, inserting a vertical line at ±20037508.34 to create a continuous visual path. The code uses ST_Transform, ST_MakeLine, and ST_Collect to handle the geometry, and defines a custom aggregate (axh_crossdateline) to process ordered point sequences. The function expects SRID 3857 inputs and returns the adjusted line for seamless rendering.

Aandrewxhill

59% match

Loading thumbnail…

Use yahoo string geolocation api and drop results into cartodb

This visualization streams text from a local file through the Yahoo Placemaker API, geolocating mentioned places and inserting their coordinates into a CartoDB table. It shows how raw text lines map to geographic points, with each detected place labeled by name, WOEID, and placetype. The Python script uses the `placemaker` library to extract locations and `urllib2` to send SQL insert statements to CartoDB’s SQL API, storing geometry via `CDB_LatLng`. Failed requests are logged to a file.

Aandrewxhill

59% match