Skip to main content
100%

parenthesis enclosed NYC open data lat/lon to the_geom

✓ Published0🌍 Public
Aandrewxhill
Last edited Oct 26, 2012
Created on Oct 26, 2012

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.

AI-generated description

Similar vizzes

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
72% match
Loading thumbnail…

PLUTO reverse geocode SQL

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.

Aandrewxhill
66% match
Loading thumbnail…

New SQL statement for bigline generation

This SQL snippet constructs a long, detailed line geometry from a sequence of latitude and longitude coordinates using PostGIS. The example demonstrates how to define a complex LINESTRING with over 180 vertices in a single SQL statement, likely for generating a "big line" for mapping or geospatial analysis. The path traced by the coordinates suggests a real-world route or boundary, showcasing the ability to handle dense, high-resolution geographic data directly within a database query.

Aandrewxhill
65% match
Loading thumbnail…

Longer single line text

This example uses PostGIS’s ST_GeometryFromText function to load a single LINESTRING of GPS track points and render it as a path. The line is drawn from over two hundred coordinate pairs tracing a route through southern Idaho, with the SQL query creating the geometric line feature. The visualization highlights how raw coordinate data can be transformed into a continuous geographic path, emphasizing the shape and direction of the route. The author, andrewxhill, sourced this from a gist, demonstrating a minimal SQL approach to spatial data handling.

Aandrewxhill
63% 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
63% match
Loading thumbnail…

Gist 6114786

This example implements a private torque-style time-series visualization for geospatial data using PostgreSQL and PostGIS. It defines custom SQL functions that aggregate point features into web-Mercator grid cells, grouping counts by time steps and returning arrays of values and dates for rendering. The code uses `ST_SnapToGrid`, `CDB_XYZ_Resolution`, and `CDB_XYZ_Extent` to tile data at a given zoom level, with two variants: a standard response for instantaneous counts and a cumulative response that includes expiration dates for each cell. A metadata function returns the spatial bounds and time range from the source table, supporting both timestamped and numeric columns.

Aandrewxhill
62% match
Loading thumbnail…

Fustion Tables CSV => Postgresql => Geom fix

This example shows a workflow for repairing invalid geometry in a FEMA damage assessment dataset stored in PostgreSQL with PostGIS. It converts KML-style polygon coordinates from a Fusion Tables CSV export into valid PostGIS geometries, then updates the spatial column. The process uses SQL string functions like `replace()` to transform coordinate formatting, `ST_GeomFromText` to parse the text into geometry, and `ST_MakePolygon` with `ST_AddPoint` to close and correct the polygons. The result prepares the data for subsequent mapping or analysis in a GIS environment.

Aandrewxhill
62% match
Loading thumbnail…

just a bookmark of random psql queries.

This example visualizes spatial relationships between neighborhoods, city blocks, and demographic data in Portland and Laurelhurst through a series of SQL queries. The code shows how geographic joins and intersections are performed using PostGIS functions like `ST_Intersects`, combining building footprints, block polygons, and census block-group data. The queries calculate race-based percentages, such as percent white and percent black, from census fields like `h7x002` and `h7x003`, while filtering for specific places and positive population counts. The gist demonstrates iterative query building for choropleth-style analysis, revealing how raw geographic and tabular data are merged to support subsequent mapping or charting in a visualization tool.

CCaged
61% match