Skip to content

Instantly share code, notes, and snippets.

@timlinux

timlinux/json-pg-query.sql

Last active January 9, 2018 12:27
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save timlinux/272bab60ffd2357921a1d2a8f5395eb2 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save timlinux/272bab60ffd2357921a1d2a8f5395eb2 to your computer and use it in GitHub Desktop.
Download ZIP
TrailMapperTrails
Raw
json-pg-query.sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
update trail set guid = uuid_generate_v4();
ALTER TABLE public.trail
ADD COLUMN geom geometry;
SELECT json_build_object(
'id', id,
'name', name,
'notes', notes,
'offset', "offset",
'colour', colour,
'image', image,
'guid', guid,
'pkuid', pkuid,
'geom', st_astext(geom)) from trail;
Raw
trail62.json
{
"name": "Die Plaat Hike",
"colour": "#ff018d",
"pkuid": 1,
"id": 62,
"geom": "POINT(0 0)",
"image": null,
"offset": 2,
"guid": "072d95e0-7254-472b-8142-35bd818a5e73",
"notes": "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0//EN\" \"http://www.w3.org/TR/REC-html40/strict.dtd\">\n<html><head><meta name=\"qrichtext\" content=\"1\" /><style type=\"text/css\">\np, li { white-space: pre-wrap; }\n</style></head><body style=\" font-family:'.SF NS Text'; font-size:13pt; font-weight:400; font-style:normal;\">\n<p style=\" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;\">NULL</p></body></html>"
}
Raw
trail_section_72.json
{
"name": "none",
"grade": 0,
"notes": "none",
"geom": "LINESTRING ZM (20.464752631843 -33.9916888367838 0 0,20.464755400639 -33.9916895384963 0 0)",
"image": null,
"guid": "77aee729-510d-47c8-8f8b-745ec2eb4883",
"id": 72
}
Raw
trail_sections.json
{
"trail_sections": [{
"name": "none",
"grade": 0,
"notes": "none",
"geom": "LINESTRING ZM (20.464752631843 -33.9916888367838 0 0,20.464755400639 -33.9916895384963 0 0)",
"image": null,
"guid": "77aee729-510d-47c8-8f8b-745ec2eb4883",
"id": 72
},
{
"name": "Jeep track",
"grade": 0,
"notes": "none",
"geom": "LINESTRING Z (20.4569393711756 -33.9990903382407 0,20.4567170605235 -33.9990026866315 0)",
"image": null,
"guid": "40739e8d-cac9-4243-880f-18aebaf698c5",
"id": 85
}
]
}
Raw
trails.json
{
"trails": [{
"name": "Die Plaat Hike",
"colour": "#ff018d",
"pkuid": 1,
"id": 62,
"geom": "POINT(0 0)",
"image": null,
"offset": 2,
"guid": "072d95e0-7254-472b-8142-35bd818a5e73",
"notes": "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0//EN\" \"http://www.w3.org/TR/REC-html40/strict.dtd\">\n<html><head><meta name=\"qrichtext\" content=\"1\" /><style type=\"text/css\">\np, li { white-space: pre-wrap; }\n</style></head><body style=\" font-family:'.SF NS Text'; font-size:13pt; font-weight:400; font-style:normal;\">\n<p style=\" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;\">NULL</p></body></html>"
},
{
"name": "5 Day Trail",
"colour": "#000000",
"pkuid": 2,
"id": 56,
"geom": "POINT(0 0)",
"image": null,
"offset": -2,
"guid": "abf786a1-7877-4de6-a0d2-44f717e7363b",
"notes": "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0//EN\" \"http://www.w3.org/TR/REC-html40/strict.dtd\">\n<html><head><meta name=\"qrichtext\" content=\"1\" /><style type=\"text/css\">\np, li { white-space: pre-wrap; }\n</style></head><body style=\" font-family:'.SF NS Text'; font-size:13pt; font-weight:400; font-style:normal;\">\n<p style=\" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;\">NULL</p></body></html>"
}
]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment