2

I am trying to insert the newly obtained data from st_dwithin into a new table.

However, it has been running since 1 day and I still can't get any data into the new table.

Can i know what is the problem?

When i do the select only, it took less than 10 seconds to return the result

sql> SELECT
            a.event_id,
            a.local_date,
            a.source,
            a.user_id,
            a.tourism_region_name,
            a.latitude,
            a.longitude,
            b.poi_latitude,
            b.poi_longitude,
            b.name,
            b.zip_code,
            b.city,
            b.group_designation,
            b.category_id,
            a.geom,
            b.geom
    FROM attraction_dwell_pre_2 a left JOIN tomo_poi_master_rto_sub2 b
        ON ST_DWithin(a.geog, b.geog, 200.00)

[2018-01-17 12:40:14] 500 rows retrieved starting from 1 in 792ms (execution: 621ms, fetching: 171ms)

Below is my code:

create table attraction_dwell_pre_3 as
(
   SELECT
            a.event_id,
            a.local_date,
            a.source,
            a.user_id,
            a.tourism_region_name,
            a.latitude,
            a.longitude,
            b.poi_latitude,
            b.poi_longitude,
            b.name,
            b.zip_code,
            b.city,
            b.group_designation,
            b.category_id,
            a.geom,
            b.geom
    FROM attraction_dwell_pre_2 a LEFT JOIN tomo_poi_master_rto_sub2 b
        ON ST_DWithin(a.geog, b.geog, 200.00)
);

*There are no index or primary key on the 2 tables

  • 1
    ST_DWithin doesn't insert features, so your question title needs to be edited. As with all SQL optimization requests, we need the table definitions, the spatial references, and an EXPLAIN plan for your statement. You should always build a spatial index before querying. – Vince Jan 17 '18 at 0:26
  • Hi @Vince, should i build spatial index on both table's geography/geometry column or only on 1 table? – Derek Jan 17 '18 at 1:10
  • @Derek on both tables, then you should cluster by it. – Evan Carroll Jan 17 '18 at 3:27
  • You might need to build multiple indexes on the same column, if you need a geodetic spatial search on a geometry column. But that's not what's causing a day of delay when creating a table based on a 10-second query. – Vince Jan 17 '18 at 4:03
  • 2
    Technically you only need an index on tomo_poi_master_rto_sub2, as you are doing a LEFT JOIN, which will necesitate a full table scan of attraction_dwell_pre_2 anyway. However, as a general rule, you should pretty much always put a spatial index on any geometry column. If you run EXPLAIN on your query, with or without indexes, you should see the difference. – John Powell Jan 17 '18 at 9:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.