0

I'm new to SQL. I wrote a query to perform a join based on a common attribute. I am having problems displaying it because the column the_geom_webmercator doesn't exist.

My question is, is my table produced after the join a "view", is it a new table I can modify? It might be important to note I am bringing in data from a PostGIS db via enterprise. Reading through the documentation it seems I should just add a new column for the_geom_webmercator and populate it with the information from the_geom. How can I do this?

Here's what I have:

SELECT
  id, name, region_id, phone_number, feature_order, cartodb_id, latitude, 
longitude, addressable_type,
  ST_SetSRID(    ST_MakePoint(
      longitude,
      latitude
    ),
    4326
  ) AS the_geom

FROM (SELECT 
_d.id, _d.name, _d.region_id, _d.phone_number, 
_d.feature_order, _add.cartodb_id, _add.latitude, _add.longitude, 
_add.addressable_type, _add.the_geom


FROM addresses AS _add
JOIN places AS _d ON _d.id = _add.addressable_id

WHERE _d.open = 'true'  AND _add.addressable_type = 'store'

) AS _camshaft_georeference_long_lat_analysis
1
  • @ramiroaznar Thanks a bunch! This worked flawlessly. Nov 6, 2017 at 23:05

1 Answer 1

2

You need to use PostGIS ST_Transform function, you can use something like this:

WITH query AS (...)
SELECT 
   *, 
   ST_Transfrom(the_geom, 3857) as the_geom_webmercator 
FROM 
  query

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.