Monday 31 October 2016

convert [year] column to date


convert [year] column from excel to date

DATEADD('year', INT([Year])-1900, #1900-01-01#)

https://community.tableau.com/message/235385

Friday 30 September 2016

Georeferrence vector in QGIS

1. get maps EPSG Projection 2953 - NAD83(CSRS) / New Brunswick Stereographic
2. clip vetor - https://www.youtube.com/watch?v=b3c_XRY7oQs
3. create control points - https://www.youtube.com/watch?v=gU05Zh_0d7A
4. captura coordenadas e cria txt:

# linear transformation from xy local to latlong
# 4 maps corners defined
# UL NW
# UR NE
# LR SW
# LL SE
x1 y1sem referencia x1 y1real                                   1    2
x2 y2sem referencia x2 y2real                                   4    3
x3 y3sem referencia x3 y3real
x4 y4sem referencia x4 y4real   

6. Processing>toolbox>advance interface>grass commands>vetor>v.transform.pointsfile

https://www.youtube.com/watch?v=XEaDKUajB5c

Thursday 29 September 2016

Create Tableau Maps from Shapefiles

https://onlinehelp.tableau.com/current/pro/desktop/en-us/maps_shapefiles.html

Create Tableau polygons from shapefiles using QGIS

Before you follow the steps in this section, the coordinates in your shapefile should already be in the form of decimal latitude and longitude units, specifically using EPSG: 4326, WGS 84.
Additionally, this procedure requires you to use QGIS Desktop, an open source GIS that supports many different shapefile formats.

Step 1: Download shapefile data

Step 2: Use QGIS Desktop to convert shapefiles to CSV file format

  1. Open QGIS Desktop.
  2. Select Plugins > Manage and Install Plugins.
  3. In the Plugins dialog box, do the following:
    • In the search bar at the top, search for mmqgis.
    • Select the mmqgis plugin that appears, and click Install plugin.
    • Click Close.
  4. On the left side of the workspace, click the Add Vector Layer icon.
  5. In the Add vector layer dialog box, do the following:
    • Under Source type, click Directory.
    • Under Source, click Browse, and then navigate to and select the US Counties folder.
    • Click Open.
  6. If the Select vector layers to add dialog box appears, select cb_2015_us_county_500k, and then click OK.
  7. The polygons in the shapefile are plotted on the canvas and a layer is added to the Layers Panel.

  8. Select Vector > Geometry Tools > Multipart to Singleparts.
    This breaks any multiple polygons in the shapefile into single polygons so that Tableau can read and plot them properly on a map view. If you skip this step, certain locations in your map view might not plot correctly because Tableau does not recognize that they might be separate polygons. For example, in the image below, some of the Wisconsin counties along Lake Superior are not plotted properly.
  9. In the Multipart to singleparts dialog box, do the following:
    • For Input line or polygon vector layer, make sure US Counties cb_2015_us_county_500k any is selected.
    • For Output shapefile, click Browse.
      In the new dialog box that opens, name the file Single Polygons and save it on your computer.
    • Click OK, and then click Close to return to the QGIS workspace.
    The Single Polygons layer is added to the Layers Panel.

  10. Select MMGIS > Import/Export > Geometry Export to CSV File.
  11. In the Export Geometry to CSV dialog box, do the following:
    • For Source Layer, make sure that Single Polygons is selected.
    • For Output Nodes CSV File, click Browse.
      In the subsequent dialog box that opens, name the file nodes.csv and save it to a location on your computer.
    • For Output Attributes CSV File, click Browse.
      In the subsequent dialog box that opens, name the file attributes.csv and save it in the same location that you saved the nodes.csv file.
    • For Line Terminator, select LF. This ensures that any spaces are removed from cells in the CSV file.
    • Click OK.
The two CSV files, nodes.csv and attributes.csv, are saved to the location you chose.

Step 3: Prepare the Nodes CSV file for Tableau

  1. Open the nodes.csv file you saved.
    Note: The CSV file used in this example contains approximately 1,037,335 rows of data. Due to worksheet size limits, you might not be able to open this file in older versions of Microsoft Excel.
  2. Add a new column called Path, which lists a sequence of numbers from 1 to however many records are in the file.
    For this example, there are 1048575 records. For shortcuts on adding a sequence of numbers in Microsoft Excel, see the Microsoft Office Support website.
  3. Save and close the file.

Step 4: Connect to CSV files in Tableau and prepare your fields for creating map views

  1. Open Tableau Desktop.
  2. On the Connect section of the start page, click Text File.
  3. Select the nodes.csv file that you created with QGIS.
  4. The nodes.csv file is added to the top of the canvas on the data source page. If you saved the nodes.csv and attributes.csv files in the same place, they both appear in the left pane.

  5. From the left pane, drag attributes.csv to the top of the canvas, to the right of the nodes.csv file.
    Tableau creates an inner join between the two data sources because they have a field in common: Shapeid.
  6. In the grid on the bottom half of the workspace, click Update Now to view your data.
  7. In the grid, for the Shapeid (Attributes) and Shapeid columns, click the data type icon and select String.

    Important: If you do not change both Shapeid columns to be string data types, the join between the two data sources breaks.
  8. In the grid, for the X column, click the data type icon and select Geographic role > Longitude.
  9. For the Y column, click the data type icon and select Geographic role > Latitude.
  10. Click Sheet 1.

Step 5: Build the map view

  1. From the Measures pane, under nodes#csv, drag Shapeid(Count) and Path up to the Dimensions part of the Data pane.
    This converts the two fields from measures to dimensions and ensures that Tableau treats these fields as discrete locations.
  2. From Measures, under nodes#csv, drag the X field to the Columns shelf, and the Y field to the Rows shelf.
    Tableau adds an aggregation to each field on the Columns and Rows shelves and creates a map view with one mark.
  3. On the Marks card, click the Mark Type drop-down arrow and select Polygon.
    This ensures that you can create a polygon map, instead of a point data map.
  4. From Dimensions, drag Shapeid to Detail on the Marks card.
    The view temporarily updates to look like the following. Next, you tell Tableau where and in which order to plot each of the polygons.
  5. From Dimensions, drag the Path field to Path on the Marks card.
    Note: If you do not see the Path option on the Marks card, check that the mark-type is set to Polygon, not Automatic or Filled Map. See step 3.
    The view updates to a legible map of the United States, with a separate polygon for every county. By adding the field Path, which is the column you manually created in the nodes.csv file, you tell Tableau which order to draw the polygons on the map.

    You can zoom in and out of the map view, adjust the color of the marks, and even customize the background map. You can also blend additional data, such as sales data, to one of the CSV files to perform additional analyses on your location data. For more information about blending geographic data, see Data Blending vs. Custom Geocoding.

Add point data from shapefiles using QGIS

You can also use QGIS Desktop to convert point data from shapefile to CSV file format, and then add that point data on a map in Tableau.
Follow the steps below to learn how to use QGIS Desktop to convert shapefile data to text files and then use that data to create a point data map in Tableau. For information on how to download QGIS Desktop, see the Before you start section.

Step 1: Download shapefile data

  1. Go to the Natural Earth website and download the Airports data.
  2. Extract the shapefiles to a folder on your computer. In this example, the folder is named Airports and is saved on the Desktop.

Step 2: Use QGIS Desktop to convert shapefiles to CSV file format

  1. Open QGIS Desktop.
  2. On the left side of the workspace, click the Add Vector Layer icon.
  3. In the Add vector layer dialog box, do the following:
    • Under Source type, click Directory.
    • Under Source, click Browse, and then navigate to and select the airports folder.
    • Click Open.
    The points in the shapefile are plotted on the canvas and a layer is added to the Layers Panel (highlighted in blue).
  4. In the Layers Panel, right-click the airports layer, then click Save As.
  5. In the Save vector layer as dialog box, do the following:
    • For Format: Select Comma Separated Value.
    • Click Browse to select the location where you want to save the file, and then click OK.

Step 3: Connect to the CSV file in Tableau and build the map view

  1. Open Tableau Desktop.
  2. On the Connect section of the start page, click Text File.
  3. Select the airports CSV file that you created with QGIS.
    The airports.csv file is added to the top of the canvas on the Data Source page.
  4. In the grid, for the X column, click the data type icon and select Geographic role > Longitude.
  5. For the Y column, click the data type icon and select Geographic role > Latitude.
  6. Click Sheet 1.
  7. On Measures, double-click the X (Longitude) and Y (Longitude) fields to add them to the Columns and Rows shelves.
    A map with a single point appears.
  8. From Dimensions, drag Name to Detail on the Marks card.
    Every airport listed in the Name field is displayed as a point on the map because each airport corresponds to a latitudinal and longitudinal value.